﻿using System;
using System.Data;
using System.Data.Common;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Shuba.Model;
using System.Collections.Generic;

namespace Shuba.Data
{
    public class Prospect
    {
        public Prospect()
        {
        }

        public List<Shuba.Model.ProspectInfo> GetListApproveUM(string VALUE, string NIP)
        {
            List<Shuba.Model.ProspectInfo> _list = new List<Shuba.Model.ProspectInfo>();
            Database mydb = DBConnection.CreateDatabase();

            string mysql = String.Format(@"DECLARE @PROCESS_DATE DATETIME, @PURGING_PERIOD TINYINT
            SELECT @PROCESS_DATE = PROCESS_DATE, @PURGING_PERIOD = PURGING_PERIOD FROM TBL_SYS_PARAMS 

            SELECT PROSPECT_ID, PROSPECT_NO, TBL_PROSPECT.AO_CODE + ' - ' + ISNULL(K.USER_NAME, '') AS AO_CODE, FULL_NAME, DATE_OF_BIRTH, PLACE_OF_BIRTH,
                    KTP_NAME, MALE_FEMALE, KTP_NO, NPWP_NO, MOTHER_NAME, ISNULL(C.MS_STD_NAME,'') MARITAL_STATUS,
                    SPOUSE_NAME, SPOUSE_DATE_OF_BIRTH, MOBILE_PHONE, OFFICE_PHONE, RESIDENTIAL_PHONE,
                    ISNULL(D.MS_STD_NAME,'') RESIDENTIAL_STATUS, YEAR_OF_STAY, ISNULL(E.MS_STD_NAME,'') OFFICE_STATUS,
                    ISNULL(A.MS_STD_NAME,'') JENIS_USAHA_DESC, LAMA_USAHA, KODYA_KAB_BUSINESS, 
                    KECAMATAN_BUSINESS, KELURAHAN_BUSINESS, ZIP_CODE_BUSINESS, ADDRESS_BUSINESS,
                    ISNULL((CASE(FUNDING_LENDING) WHEN 'F' THEN 'FUNDING' WHEN 'L' THEN 'LENDING' END),'') FUNDING_LENDING ,
                    ISNULL(F.PRODUCT_NAME, '') PRODUCT_TYPE, ISNULL(G.MS_STD_NAME,'') SALES_CHANNEL, ISNULL(RENCANA_PLAFON,0) RENCANA_PLAFON,
                    ISNULL(MST_PROSPECT_TYPE.MS_STD_NAME, '') AS PROSPECT_TYPE,
                    ISNULL(MST_DEBTOR_TYPE.MS_STD_NAME, '') AS DEBTOR_TYPE,
                    ISNULL(MST_CHANNEL.CHANNEL_NAME, '') AS CHANNELING,
                    ISNULL(H.MS_STD_NAME, '') AS [EKSTERNAL], BI_GOOD_UNTIL,
                    ISNULL(JML_TANGGUNGAN,0) JML_TANGGUNGAN, ISNULL(OMSET_PER_HARI,0) OMSET_PER_HARI, 
                    ISNULL(I.MS_STD_NAME,'') CYCLE_STATUS, UM_DECISION_DATE, NO_OF_CALLS, ISNULL(J.MS_STD_NAME,'') PROGRESS_STATUS, 
                    UM_DECISION, UM_DECISION_NOTE, CO_ASSIGNED, REPUTASI_DEBITUR, IS_DEDUP, FAP_DATE
                FROM TBL_PROSPECT_UM AS TBL_PROSPECT
                    LEFT JOIN (SELECT MS_STD_ID,MS_STD_NAME FROM TBL_MS_STANDARD WHERE MS_STD_GRP_ID = 14) AS A ON TBL_PROSPECT.JENIS_USAHA = A.MS_STD_ID 
                    LEFT JOIN (SELECT MS_STD_ID,MS_STD_NAME FROM TBL_MS_STANDARD WHERE MS_STD_GRP_ID = 4) AS B ON TBL_PROSPECT.PROGRESS_STATUS = B.MS_STD_ID 
                    LEFT JOIN (SELECT MS_STD_ID,MS_STD_NAME FROM TBL_MS_STANDARD WHERE MS_STD_GRP_ID = 3) AS C ON TBL_PROSPECT.MARITAL_STATUS = C.MS_STD_ID 
                    LEFT JOIN (SELECT MS_STD_ID,MS_STD_NAME FROM TBL_MS_STANDARD WHERE MS_STD_GRP_ID = 6) AS D ON TBL_PROSPECT.RESIDENTIAL_STATUS = D.MS_STD_ID 
                    LEFT JOIN (SELECT MS_STD_ID,MS_STD_NAME FROM TBL_MS_STANDARD WHERE MS_STD_GRP_ID = 6) AS E ON TBL_PROSPECT.OFFICE_STATUS = E.MS_STD_ID 
                    LEFT JOIN (SELECT PRODUCT_ID,PRODUCT_NAME FROM TBL_MS_PRODUCTS) AS F ON TBL_PROSPECT.PRODUCT_TYPE = F.PRODUCT_ID 
                    LEFT JOIN (SELECT MS_STD_ID,MS_STD_NAME FROM TBL_MS_STANDARD WHERE MS_STD_GRP_ID = 7) AS G ON TBL_PROSPECT.SALES_CHANNEL = G.MS_STD_ID 
                    LEFT JOIN (SELECT MS_STD_ID,MS_STD_NAME FROM TBL_MS_STANDARD WHERE MS_STD_GRP_ID = 5) AS MST_PROSPECT_TYPE ON TBL_PROSPECT.PROSPECT_TYPE = MST_PROSPECT_TYPE.MS_STD_ID 
                    LEFT JOIN (SELECT MS_STD_ID,MS_STD_NAME FROM TBL_MS_STANDARD WHERE MS_STD_GRP_ID = 9) AS MST_DEBTOR_TYPE ON TBL_PROSPECT.DEBTOR_TYPE = MST_DEBTOR_TYPE.MS_STD_ID 	
                    LEFT JOIN (SELECT CHANNEL_ID,CHANNEL_NAME FROM TBL_MS_CHANNEL) AS MST_CHANNEL ON TBL_PROSPECT.CHANNELING = MST_CHANNEL.CHANNEL_ID 
                    LEFT JOIN (SELECT MS_STD_ID,MS_STD_NAME FROM TBL_MS_STANDARD WHERE MS_STD_GRP_ID = 15) AS H ON TBL_PROSPECT.[EKSTERNAL] = H.MS_STD_ID 
                    LEFT JOIN (SELECT MS_STD_ID,MS_STD_NAME FROM TBL_MS_STANDARD WHERE MS_STD_GRP_ID = 2) AS I ON TBL_PROSPECT.CYCLE_STATUS = I.MS_STD_ID 
                    LEFT JOIN (SELECT MS_STD_ID,MS_STD_NAME FROM TBL_MS_STANDARD WHERE MS_STD_GRP_ID = 4) AS J ON TBL_PROSPECT.PROGRESS_STATUS = J.MS_STD_ID 
                    LEFT JOIN (SELECT AO_CODE, NIP, USER_NAME FROM V_USER WHERE USER_TYPE = 'SO' AND BRANCH_ID IN (SELECT BRANCH_ID FROM V_USER WHERE NIP = @NIP GROUP BY BRANCH_ID
                            UNION
                        SELECT BRANCH_ID FROM TBL_ALTERNATE_UM 
                            WHERE NIP = @NIP
                            AND (CONVERT(VARCHAR(10),@PROCESS_DATE,120) >= CONVERT(VARCHAR(10),EFF_DATE_FROM,120)) 
                            AND (CONVERT(VARCHAR(10),@PROCESS_DATE,120) <= CONVERT(VARCHAR(10),EFF_DATE_TO,120)) 
                            AND IS_ACTIVE = '1' AND ALTERNATE_FOR = 'UM' GROUP BY BRANCH_ID
                        )) AS K ON TBL_PROSPECT.AO_CODE = K.AO_CODE
                WHERE (FULL_NAME LIKE @PARAM OR ADDRESS_BUSINESS LIKE @PARAM) 
                    AND TBL_PROSPECT.IS_DELETED = '0' 
                    AND TBL_PROSPECT.UPDATE_DATE > DATEADD (day , (@PURGING_PERIOD * -30), @PROCESS_DATE)
                    AND (TBL_PROSPECT.PROSPECT_NO <> 'n/a') 
                    AND RTRIM(LTRIM(TBL_PROSPECT.UNIT_CODE)) IN 
                        (SELECT BRANCH_ID FROM V_USER WHERE NIP = @NIP GROUP BY BRANCH_ID
                            UNION
                        SELECT BRANCH_ID FROM TBL_ALTERNATE_UM 
                            WHERE NIP = @NIP
                            AND (CONVERT(VARCHAR(10),@PROCESS_DATE,120) >= CONVERT(VARCHAR(10),EFF_DATE_FROM,120)) 
                            AND (CONVERT(VARCHAR(10),@PROCESS_DATE,120) <= CONVERT(VARCHAR(10),EFF_DATE_TO,120)) 
                            AND IS_ACTIVE = '1' AND ALTERNATE_FOR = 'UM' GROUP BY BRANCH_ID
                        ) ORDER BY UNIT_CODE, UPDATE_DATE DESC");
            DbCommand mycomm = mydb.GetSqlStringCommand(mysql);
            mydb.AddInParameter(mycomm, "@PARAM", DbType.String, "%" + VALUE + "%");
            mydb.AddInParameter(mycomm, "@NIP", DbType.String, NIP);

            using (IDataReader mydr = mydb.ExecuteReader(mycomm))
            {
                while (mydr.Read())
                {
                    _list.Add(new Shuba.Model.ProspectInfo(mydr.GetString(0), mydr.GetString(1), mydr.GetString(2), mydr.GetString(3), mydr.GetDateTime(4), mydr.GetString(5), mydr.GetString(6), mydr.GetString(7), mydr.GetString(8), mydr.GetString(9), mydr.GetString(10), mydr.GetString(11), mydr.GetString(12), mydr.GetDateTime(13), mydr.GetString(14), mydr.GetString(15), mydr.GetString(16), mydr.GetString(17), mydr.GetInt16(18), mydr.GetString(19), mydr.GetString(20), mydr.GetString(21), mydr.GetString(22), mydr.GetString(23), mydr.GetString(24), mydr.GetString(25), mydr.GetString(26), mydr.GetString(27), mydr.GetString(28), mydr.GetString(29), mydr.GetDouble(30), mydr.GetString(31), mydr.GetString(32), mydr.GetString(33), mydr.GetString(34), mydr.GetDateTime(35), mydr.GetInt16(36), mydr.GetInt32(37), mydr.GetString(38), mydr.GetDateTime(39), mydr.GetInt16(40), mydr.GetString(41), mydr.GetString(42), mydr.GetString(43), mydr.GetString(44), mydr.GetString(45), mydr.GetString(46), mydr.GetDateTime(47)));
                }
                mydr.Close();
            }
            return _list;
        }

        public List<ProspectInfo> GetListApproveUMAPaging(string VALUE, string NIP, int currPage, int dataPerPage)
        {
            int barisAwal = 1 + (currPage - 1) * dataPerPage;
            int barisAkhir = barisAwal + dataPerPage - 1;
            List<ProspectInfo> list = new List<ProspectInfo>();
            Database db = DBConnection.CreateDatabase();
            string sql;
            DbCommand cmd;
            sql = @"
            DECLARE @PROCESS_DATE DATETIME, @PURGING_PERIOD TINYINT
            SELECT @PROCESS_DATE = PROCESS_DATE, @PURGING_PERIOD = PURGING_PERIOD FROM TBL_SYS_PARAMS 

            SELECT * FROM ( 
                        SELECT ROW_NUMBER() OVER (ORDER BY UPDATE_DATE DESC) AS Row, 
                        PROSPECT_ID, PROSPECT_NO, TBL_PROSPECT.AO_CODE + ' - ' + ISNULL(K.USER_NAME, '') AS AO_CODE, FULL_NAME, DATE_OF_BIRTH, PLACE_OF_BIRTH,
                    KTP_NAME, MALE_FEMALE, KTP_NO, NPWP_NO, MOTHER_NAME, ISNULL(C.MS_STD_NAME,'') MARITAL_STATUS,
                    SPOUSE_NAME, SPOUSE_DATE_OF_BIRTH, MOBILE_PHONE, OFFICE_PHONE, RESIDENTIAL_PHONE,
                    ISNULL(D.MS_STD_NAME,'') RESIDENTIAL_STATUS, YEAR_OF_STAY, ISNULL(E.MS_STD_NAME,'') OFFICE_STATUS,
                    ISNULL(A.MS_STD_NAME,'') JENIS_USAHA_DESC, LAMA_USAHA, KODYA_KAB_BUSINESS, 
                    KECAMATAN_BUSINESS, KELURAHAN_BUSINESS, ZIP_CODE_BUSINESS, ADDRESS_BUSINESS,
                    ISNULL((CASE(FUNDING_LENDING) WHEN 'F' THEN 'FUNDING' WHEN 'L' THEN 'LENDING' END),'') FUNDING_LENDING ,
                    ISNULL(F.PRODUCT_NAME, '') PRODUCT_TYPE, ISNULL(G.MS_STD_NAME,'') SALES_CHANNEL, ISNULL(RENCANA_PLAFON,0) RENCANA_PLAFON,
                    ISNULL(MST_PROSPECT_TYPE.MS_STD_NAME, '') AS PROSPECT_TYPE,
                    ISNULL(MST_DEBTOR_TYPE.MS_STD_NAME, '') AS DEBTOR_TYPE,
                    ISNULL(MST_CHANNEL.CHANNEL_NAME, '') AS CHANNELING,
                    ISNULL(H.MS_STD_NAME, '') AS [EKSTERNAL], BI_GOOD_UNTIL,
                    ISNULL(JML_TANGGUNGAN,0) JML_TANGGUNGAN, ISNULL(OMSET_PER_HARI,0) OMSET_PER_HARI,
                    ISNULL(I.MS_STD_NAME,'') CYCLE_STATUS, UM_DECISION_DATE, NO_OF_CALLS, ISNULL(J.MS_STD_NAME,'') PROGRESS_STATUS,
                    UM_DECISION, UM_DECISION_NOTE, CO_ASSIGNED, REPUTASI_DEBITUR, IS_DEDUP, FAP_DATE 
                FROM TBL_PROSPECT_UM AS TBL_PROSPECT 
                    LEFT JOIN (SELECT MS_STD_ID,MS_STD_NAME FROM TBL_MS_STANDARD WHERE MS_STD_GRP_ID = 14) AS A ON TBL_PROSPECT.JENIS_USAHA = A.MS_STD_ID 
                    LEFT JOIN (SELECT MS_STD_ID,MS_STD_NAME FROM TBL_MS_STANDARD WHERE MS_STD_GRP_ID = 4) AS B ON TBL_PROSPECT.PROGRESS_STATUS = B.MS_STD_ID 
                    LEFT JOIN (SELECT MS_STD_ID,MS_STD_NAME FROM TBL_MS_STANDARD WHERE MS_STD_GRP_ID = 3) AS C ON TBL_PROSPECT.MARITAL_STATUS = C.MS_STD_ID 
                    LEFT JOIN (SELECT MS_STD_ID,MS_STD_NAME FROM TBL_MS_STANDARD WHERE MS_STD_GRP_ID = 6) AS D ON TBL_PROSPECT.RESIDENTIAL_STATUS = D.MS_STD_ID 
                    LEFT JOIN (SELECT MS_STD_ID,MS_STD_NAME FROM TBL_MS_STANDARD WHERE MS_STD_GRP_ID = 6) AS E ON TBL_PROSPECT.OFFICE_STATUS = E.MS_STD_ID 
                    LEFT JOIN (SELECT PRODUCT_ID,PRODUCT_NAME FROM TBL_MS_PRODUCTS) AS F ON TBL_PROSPECT.PRODUCT_TYPE = F.PRODUCT_ID 
                    LEFT JOIN (SELECT MS_STD_ID,MS_STD_NAME FROM TBL_MS_STANDARD WHERE MS_STD_GRP_ID = 7) AS G ON TBL_PROSPECT.SALES_CHANNEL = G.MS_STD_ID 
                    LEFT JOIN (SELECT MS_STD_ID,MS_STD_NAME FROM TBL_MS_STANDARD WHERE MS_STD_GRP_ID = 5) AS MST_PROSPECT_TYPE ON TBL_PROSPECT.PROSPECT_TYPE = MST_PROSPECT_TYPE.MS_STD_ID 
                    LEFT JOIN (SELECT MS_STD_ID,MS_STD_NAME FROM TBL_MS_STANDARD WHERE MS_STD_GRP_ID = 9) AS MST_DEBTOR_TYPE ON TBL_PROSPECT.DEBTOR_TYPE = MST_DEBTOR_TYPE.MS_STD_ID 
                    LEFT JOIN (SELECT CHANNEL_ID,CHANNEL_NAME FROM TBL_MS_CHANNEL) AS MST_CHANNEL ON TBL_PROSPECT.CHANNELING = MST_CHANNEL.CHANNEL_ID 
                    LEFT JOIN (SELECT MS_STD_ID,MS_STD_NAME FROM TBL_MS_STANDARD WHERE MS_STD_GRP_ID = 15) AS H ON TBL_PROSPECT.[EKSTERNAL] = H.MS_STD_ID 
                    LEFT JOIN (SELECT MS_STD_ID,MS_STD_NAME FROM TBL_MS_STANDARD WHERE MS_STD_GRP_ID = 2) AS I ON TBL_PROSPECT.CYCLE_STATUS = I.MS_STD_ID 
                    LEFT JOIN (SELECT MS_STD_ID,MS_STD_NAME FROM TBL_MS_STANDARD WHERE MS_STD_GRP_ID = 4) AS J ON TBL_PROSPECT.PROGRESS_STATUS = J.MS_STD_ID 
                    LEFT JOIN (SELECT AO_CODE, NIP, USER_NAME FROM V_USER WHERE USER_TYPE = 'SO' AND BRANCH_ID IN (SELECT BRANCH_ID FROM V_USER WHERE NIP = @NIP GROUP BY BRANCH_ID 
                            UNION 
                        SELECT BRANCH_ID FROM TBL_ALTERNATE_UM 
                            WHERE NIP = @NIP 
                            AND (CONVERT(VARCHAR(10),@PROCESS_DATE,120) >= CONVERT(VARCHAR(10),EFF_DATE_FROM,120)) 
                            AND (CONVERT(VARCHAR(10),@PROCESS_DATE,120) <= CONVERT(VARCHAR(10),EFF_DATE_TO,120)) 
                            AND IS_ACTIVE = '1' AND ALTERNATE_FOR = 'UM' GROUP BY BRANCH_ID 
                        )) AS K ON TBL_PROSPECT.AO_CODE = K.AO_CODE 
                WHERE (FULL_NAME LIKE @PARAM OR ADDRESS_BUSINESS LIKE @PARAM) 
                    AND TBL_PROSPECT.IS_DELETED = '0' 
                    AND TBL_PROSPECT.UPDATE_DATE > DATEADD (day , (@PURGING_PERIOD * -30), @PROCESS_DATE) 
                    AND (TBL_PROSPECT.PROSPECT_NO <> 'n/a') 
                    AND (TBL_PROSPECT.PROGRESS_STATUS = 'PCD') -- Update By SL 30 Oct 2013, untuk menampilkan yang PCD saja
                    AND RTRIM(LTRIM(TBL_PROSPECT.UNIT_CODE)) IN 
                        (SELECT BRANCH_ID FROM V_USER WHERE NIP = @NIP GROUP BY BRANCH_ID 
                            UNION 
                        SELECT BRANCH_ID FROM TBL_ALTERNATE_UM 
                            WHERE NIP = @NIP 
                            AND (CONVERT(VARCHAR(10),@PROCESS_DATE,120) >= CONVERT(VARCHAR(10),EFF_DATE_FROM,120)) 
                            AND (CONVERT(VARCHAR(10),@PROCESS_DATE,120) <= CONVERT(VARCHAR(10),EFF_DATE_TO,120)) 
                            AND IS_ACTIVE = '1' AND ALTERNATE_FOR = 'UM' GROUP BY BRANCH_ID 
                        )  
                        ) AS LogWithRowNumbers 
                   WHERE  Row >= @BARIS_AWAL AND Row <= @BARIS_AKHIR";
            cmd = db.GetSqlStringCommand(sql);
            db.AddInParameter(cmd, "@PARAM", DbType.String, "%" + VALUE + "%");
            db.AddInParameter(cmd, "@NIP", DbType.String, NIP);
            db.AddInParameter(cmd, "@BARIS_AWAL", DbType.Int32, barisAwal);
            db.AddInParameter(cmd, "@BARIS_AKHIR", DbType.Int32, barisAkhir);
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    list.Add(new Shuba.Model.ProspectInfo(dr.GetString(1), dr.GetString(2), dr.GetString(3), dr.GetString(4), dr.GetDateTime(5), dr.GetString(6), dr.GetString(7), dr.GetString(8), dr.GetString(9), dr.GetString(10), dr.GetString(11), dr.GetString(12), dr.GetString(13), dr.GetDateTime(14), dr.GetString(15), dr.GetString(16), dr.GetString(17), dr.GetString(18), dr.GetInt16(19), dr.GetString(20), dr.GetString(21), dr.GetString(22), dr.GetString(23), dr.GetString(24), dr.GetString(25), dr.GetString(26), dr.GetString(27), dr.GetString(28), dr.GetString(29), dr.GetString(30), dr.GetDouble(31), dr.GetString(32), dr.GetString(33), dr.GetString(34), dr.GetString(35), dr.GetDateTime(36), dr.GetInt16(37), dr.GetInt32(38), dr.GetString(39), dr.GetDateTime(40), dr.GetInt16(41), dr.GetString(42), dr.GetString(43), dr.GetString(44), dr.GetString(45), dr.GetString(46), dr.GetString(47), dr.GetDateTime(48)));
                }
                dr.Close();
            }
            return list;
        }

        public int GetRowNumberApproveUM(string VALUE, string NIP)
        {
            int jmlRow = 0;
            Database db = DBConnection.CreateDatabase();
            string sql;
            DbCommand cmd;
            sql = @"DECLARE @PROCESS_DATE DATETIME, @PURGING_PERIOD TINYINT
            SELECT @PROCESS_DATE = PROCESS_DATE, @PURGING_PERIOD = PURGING_PERIOD FROM TBL_SYS_PARAMS 

            SELECT count(1) AS jmlData FROM ( 
            SELECT PROSPECT_ID
                FROM TBL_PROSPECT_UM AS TBL_PROSPECT
                    LEFT JOIN (SELECT MS_STD_ID,MS_STD_NAME FROM TBL_MS_STANDARD WHERE MS_STD_GRP_ID = 14) AS A ON TBL_PROSPECT.JENIS_USAHA = A.MS_STD_ID 
                    LEFT JOIN (SELECT MS_STD_ID,MS_STD_NAME FROM TBL_MS_STANDARD WHERE MS_STD_GRP_ID = 4) AS B ON TBL_PROSPECT.PROGRESS_STATUS = B.MS_STD_ID 
                    LEFT JOIN (SELECT MS_STD_ID,MS_STD_NAME FROM TBL_MS_STANDARD WHERE MS_STD_GRP_ID = 3) AS C ON TBL_PROSPECT.MARITAL_STATUS = C.MS_STD_ID 
                    LEFT JOIN (SELECT MS_STD_ID,MS_STD_NAME FROM TBL_MS_STANDARD WHERE MS_STD_GRP_ID = 6) AS D ON TBL_PROSPECT.RESIDENTIAL_STATUS = D.MS_STD_ID 
                    LEFT JOIN (SELECT MS_STD_ID,MS_STD_NAME FROM TBL_MS_STANDARD WHERE MS_STD_GRP_ID = 6) AS E ON TBL_PROSPECT.OFFICE_STATUS = E.MS_STD_ID 
                    LEFT JOIN (SELECT PRODUCT_ID,PRODUCT_NAME FROM TBL_MS_PRODUCTS) AS F ON TBL_PROSPECT.PRODUCT_TYPE = F.PRODUCT_ID 
                    LEFT JOIN (SELECT MS_STD_ID,MS_STD_NAME FROM TBL_MS_STANDARD WHERE MS_STD_GRP_ID = 7) AS G ON TBL_PROSPECT.SALES_CHANNEL = G.MS_STD_ID 
                    LEFT JOIN (SELECT MS_STD_ID,MS_STD_NAME FROM TBL_MS_STANDARD WHERE MS_STD_GRP_ID = 5) AS MST_PROSPECT_TYPE ON TBL_PROSPECT.PROSPECT_TYPE = MST_PROSPECT_TYPE.MS_STD_ID 
                    LEFT JOIN (SELECT MS_STD_ID,MS_STD_NAME FROM TBL_MS_STANDARD WHERE MS_STD_GRP_ID = 9) AS MST_DEBTOR_TYPE ON TBL_PROSPECT.DEBTOR_TYPE = MST_DEBTOR_TYPE.MS_STD_ID 	
                    LEFT JOIN (SELECT CHANNEL_ID,CHANNEL_NAME FROM TBL_MS_CHANNEL) AS MST_CHANNEL ON TBL_PROSPECT.CHANNELING = MST_CHANNEL.CHANNEL_ID 
                    LEFT JOIN (SELECT MS_STD_ID,MS_STD_NAME FROM TBL_MS_STANDARD WHERE MS_STD_GRP_ID = 15) AS H ON TBL_PROSPECT.[EKSTERNAL] = H.MS_STD_ID 
                    LEFT JOIN (SELECT MS_STD_ID,MS_STD_NAME FROM TBL_MS_STANDARD WHERE MS_STD_GRP_ID = 2) AS I ON TBL_PROSPECT.CYCLE_STATUS = I.MS_STD_ID 
                    LEFT JOIN (SELECT MS_STD_ID,MS_STD_NAME FROM TBL_MS_STANDARD WHERE MS_STD_GRP_ID = 4) AS J ON TBL_PROSPECT.PROGRESS_STATUS = J.MS_STD_ID 
                    LEFT JOIN (SELECT AO_CODE, NIP, USER_NAME FROM V_USER WHERE USER_TYPE = 'SO' AND BRANCH_ID IN (SELECT BRANCH_ID FROM V_USER WHERE NIP = @NIP GROUP BY BRANCH_ID
                            UNION
                        SELECT BRANCH_ID FROM TBL_ALTERNATE_UM 
                            WHERE NIP = @NIP
                            AND (CONVERT(VARCHAR(10),@PROCESS_DATE,120) >= CONVERT(VARCHAR(10),EFF_DATE_FROM,120)) 
                            AND (CONVERT(VARCHAR(10),@PROCESS_DATE,120) <= CONVERT(VARCHAR(10),EFF_DATE_TO,120)) 
                            AND IS_ACTIVE = '1' AND ALTERNATE_FOR = 'UM' GROUP BY BRANCH_ID
                        )) AS K ON TBL_PROSPECT.AO_CODE = K.AO_CODE
                WHERE (FULL_NAME LIKE @PARAM OR ADDRESS_BUSINESS LIKE @PARAM) 
                    AND TBL_PROSPECT.IS_DELETED = '0' 
                    AND TBL_PROSPECT.UPDATE_DATE > DATEADD (day , (@PURGING_PERIOD * -30), @PROCESS_DATE)
                    AND (TBL_PROSPECT.PROSPECT_NO <> 'n/a') 
                    AND (TBL_PROSPECT.PROGRESS_STATUS = 'PCD') -- Update By SL 30 Oct 2013, untuk menampilkan yang PCD saja
                    AND RTRIM(LTRIM(TBL_PROSPECT.UNIT_CODE)) IN 
                        (SELECT BRANCH_ID FROM V_USER WHERE NIP = @NIP GROUP BY BRANCH_ID
                            UNION
                        SELECT BRANCH_ID FROM TBL_ALTERNATE_UM 
                            WHERE NIP = @NIP
                            AND (CONVERT(VARCHAR(10),@PROCESS_DATE,120) >= CONVERT(VARCHAR(10),EFF_DATE_FROM,120)) 
                            AND (CONVERT(VARCHAR(10),@PROCESS_DATE,120) <= CONVERT(VARCHAR(10),EFF_DATE_TO,120)) 
                            AND IS_ACTIVE = '1' AND ALTERNATE_FOR = 'UM' GROUP BY BRANCH_ID
                        ) 
                ) AS DATA";

            cmd = db.GetSqlStringCommand(sql);
            db.AddInParameter(cmd, "@PARAM", DbType.String, "%" + VALUE + "%");
            db.AddInParameter(cmd, "@NIP", DbType.String, NIP);
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                    jmlRow = (int)dr["jmlData"];
                dr.Close();
            }
            return jmlRow;
        }

        //Update by Imam Pradana, penambahan variable status sebagai flag apakah ada record atau tidak
        //29 Oktober 2013 16.38
        //02 Jan 2013 - Add IS_OFFLINE_ENTRY (SPADA / SPAMOBILE)
        public ProspectInfo GetApproveUM(string PROSPECT_ID)
        {
            ProspectInfo en = new ProspectInfo();
            Database mydb = DBConnection.CreateDatabase();

            string mysql = String.Format(@"SELECT PROSPECT_ID, PROSPECT_NO, AO_CODE, FULL_NAME, DATE_OF_BIRTH, PLACE_OF_BIRTH,
	                                            KTP_NAME, MALE_FEMALE, KTP_NO, NPWP_NO, MOTHER_NAME, ISNULL(C.MS_STD_NAME,'') MARITAL_STATUS,
	                                            SPOUSE_NAME, SPOUSE_DATE_OF_BIRTH, MOBILE_PHONE, OFFICE_PHONE, RESIDENTIAL_PHONE,
	                                            ISNULL(D.MS_STD_NAME,'') RESIDENTIAL_STATUS, YEAR_OF_STAY, ISNULL(E.MS_STD_NAME,'') OFFICE_STATUS,
	                                            ISNULL(A.MS_STD_NAME,'') JENIS_USAHA_DESC, LAMA_USAHA, KODYA_KAB_BUSINESS, 
	                                            KECAMATAN_BUSINESS, KELURAHAN_BUSINESS, ZIP_CODE_BUSINESS, ADDRESS_BUSINESS,
	                                            ISNULL((CASE(FUNDING_LENDING) WHEN 'F' THEN 'FUNDING' WHEN 'L' THEN 'LENDING' END),'') FUNDING_LENDING ,
	                                            ISNULL(F.PRODUCT_NAME, '') PRODUCT_TYPE, ISNULL(G.MS_STD_NAME,'') SALES_CHANNEL, ISNULL(RENCANA_PLAFON,0) RENCANA_PLAFON,	                                                         
                                                ISNULL(MST_PROSPECT_TYPE.MS_STD_NAME, '') AS PROSPECT_TYPE,
                                                ISNULL(MST_DEBTOR_TYPE.MS_STD_NAME, '') AS DEBTOR_TYPE,
                                                ISNULL(MST_CHANNEL.CHANNEL_NAME, '') AS CHANNELING,
                                                ISNULL(H.MS_STD_NAME, '') AS [EKSTERNAL], BI_GOOD_UNTIL,
                                                ISNULL(JML_TANGGUNGAN,0) JML_TANGGUNGAN, ISNULL(OMSET_PER_HARI,0) OMSET_PER_HARI, ISNULL(I.MS_STD_NAME,'') CYCLE_STATUS, UM_DECISION_DATE, NO_OF_CALLS, 
                                                TBL_PROSPECT.PROGRESS_STATUS, UM_DECISION, UM_DECISION_NOTE, CO_ASSIGNED, REPUTASI_DEBITUR, IS_DEDUP, FAP_DATE, IS_OFFLINE_ENTRY
                                            FROM TBL_PROSPECT_UM AS TBL_PROSPECT
                                                LEFT JOIN (SELECT MS_STD_ID,MS_STD_NAME FROM TBL_MS_STANDARD WHERE MS_STD_GRP_ID = 14) AS A ON TBL_PROSPECT.JENIS_USAHA = A.MS_STD_ID 
                                                LEFT JOIN (SELECT MS_STD_ID,MS_STD_NAME FROM TBL_MS_STANDARD WHERE MS_STD_GRP_ID = 4) AS B ON TBL_PROSPECT.PROGRESS_STATUS = B.MS_STD_ID 
	                                            LEFT JOIN (SELECT MS_STD_ID,MS_STD_NAME FROM TBL_MS_STANDARD WHERE MS_STD_GRP_ID = 3) AS C ON TBL_PROSPECT.MARITAL_STATUS = C.MS_STD_ID 
	                                            LEFT JOIN (SELECT MS_STD_ID,MS_STD_NAME FROM TBL_MS_STANDARD WHERE MS_STD_GRP_ID = 6) AS D ON TBL_PROSPECT.RESIDENTIAL_STATUS = D.MS_STD_ID 
	                                            LEFT JOIN (SELECT MS_STD_ID,MS_STD_NAME FROM TBL_MS_STANDARD WHERE MS_STD_GRP_ID = 6) AS E ON TBL_PROSPECT.OFFICE_STATUS = E.MS_STD_ID 
	                                            LEFT JOIN (SELECT PRODUCT_ID,PRODUCT_NAME FROM TBL_MS_PRODUCTS) AS F ON TBL_PROSPECT.PRODUCT_TYPE = F.PRODUCT_ID 
	                                            LEFT JOIN (SELECT MS_STD_ID,MS_STD_NAME FROM TBL_MS_STANDARD WHERE MS_STD_GRP_ID = 7) AS G ON TBL_PROSPECT.SALES_CHANNEL = G.MS_STD_ID 
                                                LEFT JOIN (SELECT MS_STD_ID,MS_STD_NAME FROM TBL_MS_STANDARD WHERE MS_STD_GRP_ID = 5) AS MST_PROSPECT_TYPE ON TBL_PROSPECT.PROSPECT_TYPE = MST_PROSPECT_TYPE.MS_STD_ID 
                                                LEFT JOIN (SELECT MS_STD_ID,MS_STD_NAME FROM TBL_MS_STANDARD WHERE MS_STD_GRP_ID = 9) AS MST_DEBTOR_TYPE ON TBL_PROSPECT.DEBTOR_TYPE = MST_DEBTOR_TYPE.MS_STD_ID 	
                                                LEFT JOIN (SELECT CHANNEL_ID,CHANNEL_NAME FROM TBL_MS_CHANNEL) AS MST_CHANNEL ON TBL_PROSPECT.CHANNELING = MST_CHANNEL.CHANNEL_ID 
                                                LEFT JOIN (SELECT MS_STD_ID,MS_STD_NAME FROM TBL_MS_STANDARD WHERE MS_STD_GRP_ID = 15) AS H ON TBL_PROSPECT.[EKSTERNAL] = H.MS_STD_ID 
                                                LEFT JOIN (SELECT MS_STD_ID,MS_STD_NAME FROM TBL_MS_STANDARD WHERE MS_STD_GRP_ID = 2) AS I ON TBL_PROSPECT.CYCLE_STATUS = I.MS_STD_ID 
                                                LEFT JOIN (SELECT MS_STD_ID,MS_STD_NAME FROM TBL_MS_STANDARD WHERE MS_STD_GRP_ID = 4) AS J ON TBL_PROSPECT.PROGRESS_STATUS = J.MS_STD_ID 
                                        WHERE RTRIM(LTRIM(TBL_PROSPECT.PROSPECT_ID)) = @PROSPECT_ID");
            DbCommand mycomm = mydb.GetSqlStringCommand(mysql);
            mydb.AddInParameter(mycomm, "@PROSPECT_ID", DbType.String, PROSPECT_ID);

            using (IDataReader mydr = mydb.ExecuteReader(mycomm))
            {
                while (mydr.Read())
                {
                    en = new Shuba.Model.ProspectInfo(mydr.GetString(0), mydr.GetString(1), mydr.GetString(2), mydr.GetString(3), mydr.GetDateTime(4), mydr.GetString(5), mydr.GetString(6), mydr.GetString(7), mydr.GetString(8), mydr.GetString(9), mydr.GetString(10), mydr.GetString(11), mydr.GetString(12), mydr.GetDateTime(13), mydr.GetString(14), mydr.GetString(15), mydr.GetString(16), mydr.GetString(17), mydr.GetInt16(18), mydr.GetString(19), mydr.GetString(20), mydr.GetString(21), mydr.GetString(22), mydr.GetString(23), mydr.GetString(24), mydr.GetString(25), mydr.GetString(26), mydr.GetString(27), mydr.GetString(28), mydr.GetString(29), mydr.GetDouble(30), mydr.GetString(31), mydr.GetString(32), mydr.GetString(33), mydr.GetString(34), mydr.GetDateTime(35), mydr.GetInt16(36), mydr.GetInt32(37), mydr.GetString(38), mydr.GetDateTime(39), mydr.GetInt16(40), mydr.GetString(41), mydr.GetString(42), mydr.GetString(43), mydr.GetString(44), mydr.GetString(45), mydr.GetString(46), mydr.GetDateTime(47));
                    en.IS_OFFLINE_ENTRY = mydr.GetString(mydr.GetOrdinal("IS_OFFLINE_ENTRY"));
                }
                mydr.Close();
            }
            return en;
        }

        public void SaveProspect(ProspectInfo TBL_PROSPECT_TMP)
        {
            Database db = DBConnection.CreateDatabase();

            DbCommand cmd = db.GetSqlStringCommand(@"
                                UPDATE TBL_PROSPECT_TMP
                                SET 
                                    PROSPECT_NO = @PROSPECT_NO
                                    ,UNIT_CODE = @UNIT_CODE
                                    ,SOURCE_CODE = @SOURCE_CODE
                                    ,AO_CODE = @AO_CODE
                                    ,FULL_NAME = @FULL_NAME
                                    ,KTP_NAME = @KTP_NAME
                                    ,KTP_NO = @KTP_NO
                                    ,DATE_OF_BIRTH = @DATE_OF_BIRTH
                                    ,PLACE_OF_BIRTH = @PLACE_OF_BIRTH
                                    ,MOTHER_NAME = @MOTHER_NAME
                                    ,MALE_FEMALE = @MALE_FEMALE
                                    ,MARITAL_STATUS = @MARITAL_STATUS
                                    ,SPOUSE_NAME = @SPOUSE_NAME
                                    ,SPOUSE_DATE_OF_BIRTH = @SPOUSE_DATE_OF_BIRTH
                                    ,NPWP_NO = @NPWP_NO
                                    ,KODYA_KAB_BUSINESS = @KODYA_KAB_BUSINESS
                                    ,KECAMATAN_BUSINESS = @KECAMATAN_BUSINESS
                                    ,KELURAHAN_BUSINESS = @KELURAHAN_BUSINESS
                                    ,ZIP_CODE_BUSINESS = @ZIP_CODE_BUSINESS
                                    ,ADDRESS_BUSINESS = @ADDRESS_BUSINESS
                                    ,RESIDENTIAL_STATUS = @RESIDENTIAL_STATUS
                                    ,RESIDENTIAL_PHONE = @RESIDENTIAL_PHONE
                                    ,MOBILE_PHONE = @MOBILE_PHONE
                                    ,OFFICE_STATUS = @OFFICE_STATUS
                                    ,OFFICE_PHONE = @OFFICE_PHONE
                                    ,YEAR_OF_STAY = @YEAR_OF_STAY
                                    ,JML_TANGGUNGAN = @JML_TANGGUNGAN
                                    ,JENIS_USAHA = @JENIS_USAHA
                                    ,LAMA_USAHA = @LAMA_USAHA
                                    ,OMSET_PER_HARI = @OMSET_PER_HARI
                                    ,ADA_REK_BANK = @ADA_REK_BANK
                                    ,RENCANA_PLAFON = @RENCANA_PLAFON
                                    ,SALES_CHANNEL = @SALES_CHANNEL
                                    ,PROSPECT_TYPE = @PROSPECT_TYPE
                                    ,FUNDING_LENDING = @FUNDING_LENDING
                                    ,RESTRUCT_COMPONENT = @RESTRUCT_COMPONENT
                                    ,PROGRESS_STATUS = @PROGRESS_STATUS
                                    ,CYCLE_STATUS = @CYCLE_STATUS
                                    ,INSTALLMENT = @INSTALLMENT
                                    ,FAP_DATE = @FAP_DATE
                                    ,NO_OF_CALLS = @NO_OF_CALLS
                                    ,IS_INTERESTED = @IS_INTERESTED
                                    ,NOT_INTERESTED_REASON = @NOT_INTERESTED_REASON
                                    ,NOT_INTERESTED_NOTE = @NOT_INTERESTED_NOTE
                                    ,KET_KENDALA = @KET_KENDALA
                                    ,SCM_NOTE = @SCM_NOTE
                                    ,PRODUCT_TYPE = @PRODUCT_TYPE
                                    ,CIF_NUMBER = @CIF_NUMBER
                                    ,KARAKTER = @KARAKTER
                                    ,REPUTASI_DEBITUR = @REPUTASI_DEBITUR
                                    ,RIWAYAT_DSP = @RIWAYAT_DSP
                                    ,TAKSIRAN_PLAFON = @TAKSIRAN_PLAFON
                                    ,TAKSIRAN_USAHA = @TAKSIRAN_USAHA
                                    ,IS_DOCUMENT_COMPLETED = @IS_DOCUMENT_COMPLETED
                                    ,UM_DECISION = @UM_DECISION
                                    ,UM_DECISION_NOTE = @UM_DECISION_NOTE
                                    ,UM_DECISION_DATE = @UM_DECISION_DATE
                                    ,LOAN_NUMBER = @LOAN_NUMBER
                                    ,PRODUCT_CODE = @PRODUCT_CODE
                                    ,CURRENT_OS = @CURRENT_OS
                                    ,CURRENT_INSTALLMENT = @CURRENT_INSTALLMENT
                                    ,OFFER_PLAFON = @OFFER_PLAFON
                                    ,OFFER_INSTALLMENT = @OFFER_INSTALLMENT
                                    ,OFFER_INTEREST = @OFFER_INTEREST
                                    ,OFFER_TENOR = @OFFER_TENOR
                                    ,OFFER_FRESH_MONEY = @OFFER_FRESH_MONEY
                                    ,PRIORITY = @PRIORITY
                                    ,PIPELINE_START_TIME = @PIPELINE_START_TIME
                                    ,PIPELINE_FINISH_TIME = @PIPELINE_FINISH_TIME
                                    ,PROSPECT_START_TIME = @PROSPECT_START_TIME
                                    ,PROSPECT_FINISH_TIME = @PROSPECT_FINISH_TIME
                                    ,BI_GOOD_UNTIL = @BI_GOOD_UNTIL
                                    ,IS_BI_CHECKING = @IS_BI_CHECKING
                                    ,IS_OFFLINE_ENTRY = @IS_OFFLINE_ENTRY
                                    ,ENTRY_BY = @ENTRY_BY
                                    ,ENTRY_DATE = @ENTRY_DATE
                                    ,UPDATE_BY = @UPDATE_BY
                                    ,UPDATE_DATE = @UPDATE_DATE
                                    ,IS_BY_UPLOAD = @IS_BY_UPLOAD
                                    ,UPLOAD_BY = @UPLOAD_BY
                                    ,UPLOAD_DATE = @UPLOAD_DATE
                                    ,IS_DELETED = @IS_DELETED
                                    ,DEBTOR_TYPE = @DEBTOR_TYPE
                                    ,GU_FUP = @GU_FUP
                                    ,GU_CLOSE_DEAL = @GU_CLOSE_DEAL
                                    ,IS_DEDUP = @IS_DEDUP
                                    ,CHANNELING = @CHANNELING
                                    ,OMZET_1 = @OMZET_1
                                    ,OMZET_2 = @OMZET_2
                                    ,OMZET_3 = @OMZET_3
                                    ,OMZET_4 = @OMZET_4
                                    ,OMZET_5 = @OMZET_5
                                    ,OMZET_6 = @OMZET_6
                                    ,OMZET_7 = @OMZET_7
                                    ,OMZET_8 = @OMZET_8
                                    ,OMZET_9 = @OMZET_9
                                    ,OMZET_10 = @OMZET_10
                                    ,OMZET_11 = @OMZET_11
                                    ,OMZET_12 = @OMZET_12
                                    ,OMZET_AVERAGE = @OMZET_AVERAGE
                                    ,IS_PIPELINE_KP = @IS_PIPELINE_KP
                                WHERE (PROSPECT_ID = @PROSPECT_ID);
                                IF @@ROWCOUNT = 0
                                INSERT INTO TBL_PROSPECT_TMP
                                (
                                    PROSPECT_ID
                                    ,PROSPECT_NO
                                    ,UNIT_CODE
                                    ,SOURCE_CODE
                                    ,AO_CODE
                                    ,FULL_NAME
                                    ,KTP_NAME
                                    ,KTP_NO
                                    ,DATE_OF_BIRTH
                                    ,PLACE_OF_BIRTH
                                    ,MOTHER_NAME
                                    ,MALE_FEMALE
                                    ,MARITAL_STATUS
                                    ,SPOUSE_NAME
                                    ,SPOUSE_DATE_OF_BIRTH
                                    ,NPWP_NO
                                    ,KODYA_KAB_BUSINESS
                                    ,KECAMATAN_BUSINESS
                                    ,KELURAHAN_BUSINESS
                                    ,ZIP_CODE_BUSINESS
                                    ,ADDRESS_BUSINESS
                                    ,RESIDENTIAL_STATUS
                                    ,RESIDENTIAL_PHONE
                                    ,MOBILE_PHONE
                                    ,OFFICE_STATUS
                                    ,OFFICE_PHONE
                                    ,YEAR_OF_STAY
                                    ,JML_TANGGUNGAN
                                    ,JENIS_USAHA
                                    ,LAMA_USAHA
                                    ,OMSET_PER_HARI
                                    ,ADA_REK_BANK
                                    ,RENCANA_PLAFON
                                    ,SALES_CHANNEL
                                    ,PROSPECT_TYPE
                                    ,FUNDING_LENDING
                                    ,RESTRUCT_COMPONENT
                                    ,PROGRESS_STATUS
                                    ,CYCLE_STATUS
                                    ,INSTALLMENT
                                    ,FAP_DATE
                                    ,NO_OF_CALLS
                                    ,IS_INTERESTED
                                    ,NOT_INTERESTED_REASON
                                    ,NOT_INTERESTED_NOTE
                                    ,KET_KENDALA
                                    ,SCM_NOTE
                                    ,PRODUCT_TYPE
                                    ,CIF_NUMBER
                                    ,KARAKTER
                                    ,REPUTASI_DEBITUR
                                    ,RIWAYAT_DSP
                                    ,TAKSIRAN_PLAFON
                                    ,TAKSIRAN_USAHA
                                    ,IS_DOCUMENT_COMPLETED
                                    ,UM_DECISION
                                    ,UM_DECISION_NOTE
                                    ,UM_DECISION_DATE
                                    ,LOAN_NUMBER
                                    ,PRODUCT_CODE
                                    ,CURRENT_OS
                                    ,CURRENT_INSTALLMENT
                                    ,OFFER_PLAFON
                                    ,OFFER_INSTALLMENT
                                    ,OFFER_INTEREST
                                    ,OFFER_TENOR
                                    ,OFFER_FRESH_MONEY
                                    ,PRIORITY
                                    ,PIPELINE_START_TIME
                                    ,PIPELINE_FINISH_TIME
                                    ,PROSPECT_START_TIME
                                    ,PROSPECT_FINISH_TIME
                                    ,BI_GOOD_UNTIL
                                    ,IS_BI_CHECKING
                                    ,IS_OFFLINE_ENTRY
                                    ,ENTRY_BY
                                    ,ENTRY_DATE
                                    ,UPDATE_BY
                                    ,UPDATE_DATE
                                    ,IS_BY_UPLOAD
                                    ,UPLOAD_BY
                                    ,UPLOAD_DATE
                                    ,IS_DELETED
                                    ,DEBTOR_TYPE
                                    ,GU_FUP
                                    ,GU_CLOSE_DEAL
                                    ,IS_DEDUP
                                    ,CHANNELING
                                    ,OMZET_1
                                    ,OMZET_2
                                    ,OMZET_3
                                    ,OMZET_4
                                    ,OMZET_5
                                    ,OMZET_6
                                    ,OMZET_7
                                    ,OMZET_8
                                    ,OMZET_9
                                    ,OMZET_10
                                    ,OMZET_11
                                    ,OMZET_12
                                    ,OMZET_AVERAGE
                                    ,IS_PIPELINE_KP
                                ) 
                                VALUES
                                (
                                    @PROSPECT_ID
                                    ,@PROSPECT_NO
                                    ,@UNIT_CODE
                                    ,@SOURCE_CODE
                                    ,@AO_CODE
                                    ,@FULL_NAME
                                    ,@KTP_NAME
                                    ,@KTP_NO
                                    ,@DATE_OF_BIRTH
                                    ,@PLACE_OF_BIRTH
                                    ,@MOTHER_NAME
                                    ,@MALE_FEMALE
                                    ,@MARITAL_STATUS
                                    ,@SPOUSE_NAME
                                    ,@SPOUSE_DATE_OF_BIRTH
                                    ,@NPWP_NO
                                    ,@KODYA_KAB_BUSINESS
                                    ,@KECAMATAN_BUSINESS
                                    ,@KELURAHAN_BUSINESS
                                    ,@ZIP_CODE_BUSINESS
                                    ,@ADDRESS_BUSINESS
                                    ,@RESIDENTIAL_STATUS
                                    ,@RESIDENTIAL_PHONE
                                    ,@MOBILE_PHONE
                                    ,@OFFICE_STATUS
                                    ,@OFFICE_PHONE
                                    ,@YEAR_OF_STAY
                                    ,@JML_TANGGUNGAN
                                    ,@JENIS_USAHA
                                    ,@LAMA_USAHA
                                    ,@OMSET_PER_HARI
                                    ,@ADA_REK_BANK
                                    ,@RENCANA_PLAFON
                                    ,@SALES_CHANNEL
                                    ,@PROSPECT_TYPE
                                    ,@FUNDING_LENDING
                                    ,@RESTRUCT_COMPONENT
                                    ,@PROGRESS_STATUS
                                    ,@CYCLE_STATUS
                                    ,@INSTALLMENT
                                    ,@FAP_DATE
                                    ,@NO_OF_CALLS
                                    ,@IS_INTERESTED
                                    ,@NOT_INTERESTED_REASON
                                    ,@NOT_INTERESTED_NOTE
                                    ,@KET_KENDALA
                                    ,@SCM_NOTE
                                    ,@PRODUCT_TYPE
                                    ,@CIF_NUMBER
                                    ,@KARAKTER
                                    ,@REPUTASI_DEBITUR
                                    ,@RIWAYAT_DSP
                                    ,@TAKSIRAN_PLAFON
                                    ,@TAKSIRAN_USAHA
                                    ,@IS_DOCUMENT_COMPLETED
                                    ,@UM_DECISION
                                    ,@UM_DECISION_NOTE
                                    ,@UM_DECISION_DATE
                                    ,@LOAN_NUMBER
                                    ,@PRODUCT_CODE
                                    ,@CURRENT_OS
                                    ,@CURRENT_INSTALLMENT
                                    ,@OFFER_PLAFON
                                    ,@OFFER_INSTALLMENT
                                    ,@OFFER_INTEREST
                                    ,@OFFER_TENOR
                                    ,@OFFER_FRESH_MONEY
                                    ,@PRIORITY
                                    ,@PIPELINE_START_TIME
                                    ,@PIPELINE_FINISH_TIME
                                    ,@PROSPECT_START_TIME
                                    ,@PROSPECT_FINISH_TIME
                                    ,@BI_GOOD_UNTIL
                                    ,@IS_BI_CHECKING
                                    ,@IS_OFFLINE_ENTRY
                                    ,@ENTRY_BY
                                    ,@ENTRY_DATE
                                    ,@UPDATE_BY
                                    ,@UPDATE_DATE
                                    ,@IS_BY_UPLOAD
                                    ,@UPLOAD_BY
                                    ,@UPLOAD_DATE
                                    ,@IS_DELETED
                                    ,@DEBTOR_TYPE
                                    ,@GU_FUP
                                    ,@GU_CLOSE_DEAL
                                    ,@IS_DEDUP
                                    ,@CHANNELING
                                    ,@OMZET_1
                                    ,@OMZET_2
                                    ,@OMZET_3
                                    ,@OMZET_4
                                    ,@OMZET_5
                                    ,@OMZET_6
                                    ,@OMZET_7
                                    ,@OMZET_8
                                    ,@OMZET_9
                                    ,@OMZET_10
                                    ,@OMZET_11
                                    ,@OMZET_12
                                    ,@OMZET_AVERAGE
                                    ,@IS_PIPELINE_KP
                                );
                            ");
            db.AddInParameter(cmd, "@PROSPECT_ID", DbType.String, TBL_PROSPECT_TMP.PROSPECT_ID);
            db.AddInParameter(cmd, "@PROSPECT_NO", DbType.String, TBL_PROSPECT_TMP.PROSPECT_NO);
            db.AddInParameter(cmd, "@UNIT_CODE", DbType.String, TBL_PROSPECT_TMP.UNIT_CODE);
            db.AddInParameter(cmd, "@SOURCE_CODE", DbType.String, TBL_PROSPECT_TMP.SOURCE_CODE);
            db.AddInParameter(cmd, "@AO_CODE", DbType.String, TBL_PROSPECT_TMP.AO_CODE);
            db.AddInParameter(cmd, "@FULL_NAME", DbType.String, TBL_PROSPECT_TMP.FULL_NAME);
            db.AddInParameter(cmd, "@KTP_NAME", DbType.String, TBL_PROSPECT_TMP.KTP_NAME);
            db.AddInParameter(cmd, "@KTP_NO", DbType.String, TBL_PROSPECT_TMP.KTP_NO);
            db.AddInParameter(cmd, "@DATE_OF_BIRTH", DbType.DateTime, TBL_PROSPECT_TMP.DATE_OF_BIRTH);
            db.AddInParameter(cmd, "@PLACE_OF_BIRTH", DbType.String, TBL_PROSPECT_TMP.PLACE_OF_BIRTH);
            db.AddInParameter(cmd, "@MOTHER_NAME", DbType.String, TBL_PROSPECT_TMP.MOTHER_NAME);
            db.AddInParameter(cmd, "@MALE_FEMALE", DbType.String, TBL_PROSPECT_TMP.MALE_FEMALE);
            db.AddInParameter(cmd, "@MARITAL_STATUS", DbType.String, TBL_PROSPECT_TMP.MARITAL_STATUS);
            db.AddInParameter(cmd, "@SPOUSE_NAME", DbType.String, TBL_PROSPECT_TMP.SPOUSE_NAME);
            db.AddInParameter(cmd, "@SPOUSE_DATE_OF_BIRTH", DbType.DateTime, TBL_PROSPECT_TMP.SPOUSE_DATE_OF_BIRTH);
            db.AddInParameter(cmd, "@NPWP_NO", DbType.String, TBL_PROSPECT_TMP.NPWP_NO);
            db.AddInParameter(cmd, "@KODYA_KAB_BUSINESS", DbType.String, TBL_PROSPECT_TMP.KODYA_KAB_BUSINESS);
            db.AddInParameter(cmd, "@KECAMATAN_BUSINESS", DbType.String, TBL_PROSPECT_TMP.KECAMATAN_BUSINESS);
            db.AddInParameter(cmd, "@KELURAHAN_BUSINESS", DbType.String, TBL_PROSPECT_TMP.KELURAHAN_BUSINESS);
            db.AddInParameter(cmd, "@ZIP_CODE_BUSINESS", DbType.String, TBL_PROSPECT_TMP.ZIP_CODE_BUSINESS);
            db.AddInParameter(cmd, "@ADDRESS_BUSINESS", DbType.String, TBL_PROSPECT_TMP.ADDRESS_BUSINESS);
            db.AddInParameter(cmd, "@RESIDENTIAL_STATUS", DbType.String, TBL_PROSPECT_TMP.RESIDENTIAL_STATUS);
            db.AddInParameter(cmd, "@RESIDENTIAL_PHONE", DbType.String, TBL_PROSPECT_TMP.RESIDENTIAL_PHONE);
            db.AddInParameter(cmd, "@MOBILE_PHONE", DbType.String, TBL_PROSPECT_TMP.MOBILE_PHONE);
            db.AddInParameter(cmd, "@OFFICE_STATUS", DbType.String, TBL_PROSPECT_TMP.OFFICE_STATUS);
            db.AddInParameter(cmd, "@OFFICE_PHONE", DbType.String, TBL_PROSPECT_TMP.OFFICE_PHONE);
            db.AddInParameter(cmd, "@YEAR_OF_STAY", DbType.Int16, TBL_PROSPECT_TMP.YEAR_OF_STAY);
            db.AddInParameter(cmd, "@JML_TANGGUNGAN", DbType.Int16, TBL_PROSPECT_TMP.JML_TANGGUNGAN);
            db.AddInParameter(cmd, "@JENIS_USAHA", DbType.String, TBL_PROSPECT_TMP.JENIS_USAHA);
            db.AddInParameter(cmd, "@LAMA_USAHA", DbType.String, TBL_PROSPECT_TMP.LAMA_USAHA);
            db.AddInParameter(cmd, "@OMSET_PER_HARI", DbType.Int32, TBL_PROSPECT_TMP.OMSET_PER_HARI);
            db.AddInParameter(cmd, "@ADA_REK_BANK", DbType.String, TBL_PROSPECT_TMP.ADA_REK_BANK);
            db.AddInParameter(cmd, "@RENCANA_PLAFON", DbType.Double, TBL_PROSPECT_TMP.RENCANA_PLAFON);
            db.AddInParameter(cmd, "@SALES_CHANNEL", DbType.String, TBL_PROSPECT_TMP.SALES_CHANNEL);
            db.AddInParameter(cmd, "@PROSPECT_TYPE", DbType.String, TBL_PROSPECT_TMP.PROSPECT_TYPE);
            db.AddInParameter(cmd, "@FUNDING_LENDING", DbType.String, TBL_PROSPECT_TMP.FUNDING_LENDING);
            db.AddInParameter(cmd, "@RESTRUCT_COMPONENT", DbType.String, TBL_PROSPECT_TMP.RESTRUCT_COMPONENT);
            db.AddInParameter(cmd, "@PROGRESS_STATUS", DbType.String, TBL_PROSPECT_TMP.PROGRESS_STATUS);
            db.AddInParameter(cmd, "@CYCLE_STATUS", DbType.String, TBL_PROSPECT_TMP.CYCLE_STATUS);
            db.AddInParameter(cmd, "@INSTALLMENT", DbType.Double, TBL_PROSPECT_TMP.INSTALLMENT);
            db.AddInParameter(cmd, "@FAP_DATE", DbType.DateTime, TBL_PROSPECT_TMP.FAP_DATE);
            db.AddInParameter(cmd, "@NO_OF_CALLS", DbType.Int16, TBL_PROSPECT_TMP.NO_OF_CALLS);
            db.AddInParameter(cmd, "@IS_INTERESTED", DbType.String, TBL_PROSPECT_TMP.IS_INTERESTED);
            db.AddInParameter(cmd, "@NOT_INTERESTED_REASON", DbType.String, TBL_PROSPECT_TMP.NOT_INTERESTED_REASON);
            db.AddInParameter(cmd, "@NOT_INTERESTED_NOTE", DbType.String, TBL_PROSPECT_TMP.NOT_INTERESTED_NOTE);
            db.AddInParameter(cmd, "@KET_KENDALA", DbType.String, TBL_PROSPECT_TMP.KET_KENDALA);
            db.AddInParameter(cmd, "@SCM_NOTE", DbType.String, TBL_PROSPECT_TMP.SCM_NOTE);
            db.AddInParameter(cmd, "@PRODUCT_TYPE", DbType.Int16, TBL_PROSPECT_TMP.PRODUCT_TYPE);
            db.AddInParameter(cmd, "@CIF_NUMBER", DbType.Int32, TBL_PROSPECT_TMP.CIF_NUMBER);
            db.AddInParameter(cmd, "@KARAKTER", DbType.String, TBL_PROSPECT_TMP.KARAKTER);
            db.AddInParameter(cmd, "@REPUTASI_DEBITUR", DbType.String, TBL_PROSPECT_TMP.REPUTASI_DEBITUR);
            db.AddInParameter(cmd, "@RIWAYAT_DSP", DbType.String, TBL_PROSPECT_TMP.RIWAYAT_DSP);
            db.AddInParameter(cmd, "@TAKSIRAN_PLAFON", DbType.Double, TBL_PROSPECT_TMP.TAKSIRAN_PLAFON);
            db.AddInParameter(cmd, "@TAKSIRAN_USAHA", DbType.String, TBL_PROSPECT_TMP.TAKSIRAN_USAHA);
            db.AddInParameter(cmd, "@IS_DOCUMENT_COMPLETED", DbType.String, TBL_PROSPECT_TMP.IS_DOCUMENT_COMPLETED);
            db.AddInParameter(cmd, "@UM_DECISION", DbType.String, TBL_PROSPECT_TMP.UM_DECISION);
            db.AddInParameter(cmd, "@UM_DECISION_NOTE", DbType.String, TBL_PROSPECT_TMP.UM_DECISION_NOTE);
            db.AddInParameter(cmd, "@UM_DECISION_DATE", DbType.DateTime, TBL_PROSPECT_TMP.UM_DECISION_DATE);
            db.AddInParameter(cmd, "@LOAN_NUMBER", DbType.String, TBL_PROSPECT_TMP.LOAN_NUMBER);
            db.AddInParameter(cmd, "@PRODUCT_CODE", DbType.String, TBL_PROSPECT_TMP.PRODUCT_CODE);
            db.AddInParameter(cmd, "@CURRENT_OS", DbType.Double, TBL_PROSPECT_TMP.CURRENT_OS);
            db.AddInParameter(cmd, "@CURRENT_INSTALLMENT", DbType.Double, TBL_PROSPECT_TMP.CURRENT_INSTALLMENT);
            db.AddInParameter(cmd, "@OFFER_PLAFON", DbType.Double, TBL_PROSPECT_TMP.OFFER_PLAFON);
            db.AddInParameter(cmd, "@OFFER_INSTALLMENT", DbType.Double, TBL_PROSPECT_TMP.OFFER_INSTALLMENT);
            db.AddInParameter(cmd, "@OFFER_INTEREST", DbType.String, TBL_PROSPECT_TMP.OFFER_INTEREST);
            db.AddInParameter(cmd, "@OFFER_TENOR", DbType.Int16, TBL_PROSPECT_TMP.OFFER_TENOR);
            db.AddInParameter(cmd, "@OFFER_FRESH_MONEY", DbType.Double, TBL_PROSPECT_TMP.OFFER_FRESH_MONEY);
            db.AddInParameter(cmd, "@PRIORITY", DbType.String, TBL_PROSPECT_TMP.PRIORITY);
            db.AddInParameter(cmd, "@PIPELINE_START_TIME", DbType.DateTime, TBL_PROSPECT_TMP.PIPELINE_START_TIME);
            db.AddInParameter(cmd, "@PIPELINE_FINISH_TIME", DbType.DateTime, TBL_PROSPECT_TMP.PIPELINE_FINISH_TIME);
            db.AddInParameter(cmd, "@PROSPECT_START_TIME", DbType.DateTime, TBL_PROSPECT_TMP.PROSPECT_START_TIME);
            db.AddInParameter(cmd, "@PROSPECT_FINISH_TIME", DbType.DateTime, TBL_PROSPECT_TMP.PROSPECT_FINISH_TIME);
            db.AddInParameter(cmd, "@BI_GOOD_UNTIL", DbType.DateTime, TBL_PROSPECT_TMP.BI_GOOD_UNTIL);
            db.AddInParameter(cmd, "@IS_BI_CHECKING", DbType.String, TBL_PROSPECT_TMP.IS_BI_CHECKING);
            db.AddInParameter(cmd, "@IS_OFFLINE_ENTRY", DbType.String, TBL_PROSPECT_TMP.IS_OFFLINE_ENTRY);
            db.AddInParameter(cmd, "@ENTRY_BY", DbType.String, TBL_PROSPECT_TMP.ENTRY_BY);
            db.AddInParameter(cmd, "@ENTRY_DATE", DbType.DateTime, TBL_PROSPECT_TMP.ENTRY_DATE);
            db.AddInParameter(cmd, "@UPDATE_BY", DbType.String, TBL_PROSPECT_TMP.UPDATE_BY);
            db.AddInParameter(cmd, "@UPDATE_DATE", DbType.DateTime, TBL_PROSPECT_TMP.UPDATE_DATE);
            db.AddInParameter(cmd, "@IS_BY_UPLOAD", DbType.String, TBL_PROSPECT_TMP.IS_BY_UPLOAD);
            db.AddInParameter(cmd, "@UPLOAD_BY", DbType.String, TBL_PROSPECT_TMP.UPLOAD_BY);
            db.AddInParameter(cmd, "@UPLOAD_DATE", DbType.DateTime, TBL_PROSPECT_TMP.UPLOAD_DATE);
            db.AddInParameter(cmd, "@IS_DELETED", DbType.String, TBL_PROSPECT_TMP.IS_DELETED);
            db.AddInParameter(cmd, "@DEBTOR_TYPE", DbType.String, TBL_PROSPECT_TMP.DEBTOR_TYPE);
            db.AddInParameter(cmd, "@GU_FUP", DbType.DateTime, TBL_PROSPECT_TMP.GU_FUP);
            db.AddInParameter(cmd, "@GU_CLOSE_DEAL", DbType.DateTime, TBL_PROSPECT_TMP.GU_CLOSE_DEAL);
            db.AddInParameter(cmd, "@IS_DEDUP", DbType.String, TBL_PROSPECT_TMP.IS_DEDUP);
            db.AddInParameter(cmd, "@CHANNELING", DbType.String, TBL_PROSPECT_TMP.CHANNELING);
            db.AddInParameter(cmd, "@OMZET_1", DbType.Double, TBL_PROSPECT_TMP.OMZET_1);
            db.AddInParameter(cmd, "@OMZET_2", DbType.Double, TBL_PROSPECT_TMP.OMZET_2);
            db.AddInParameter(cmd, "@OMZET_3", DbType.Double, TBL_PROSPECT_TMP.OMZET_3);
            db.AddInParameter(cmd, "@OMZET_4", DbType.Double, TBL_PROSPECT_TMP.OMZET_4);
            db.AddInParameter(cmd, "@OMZET_5", DbType.Double, TBL_PROSPECT_TMP.OMZET_5);
            db.AddInParameter(cmd, "@OMZET_6", DbType.Double, TBL_PROSPECT_TMP.OMZET_6);
            db.AddInParameter(cmd, "@OMZET_7", DbType.Double, TBL_PROSPECT_TMP.OMZET_7);
            db.AddInParameter(cmd, "@OMZET_8", DbType.Double, TBL_PROSPECT_TMP.OMZET_8);
            db.AddInParameter(cmd, "@OMZET_9", DbType.Double, TBL_PROSPECT_TMP.OMZET_9);
            db.AddInParameter(cmd, "@OMZET_10", DbType.Double, TBL_PROSPECT_TMP.OMZET_10);
            db.AddInParameter(cmd, "@OMZET_11", DbType.Double, TBL_PROSPECT_TMP.OMZET_11);
            db.AddInParameter(cmd, "@OMZET_12", DbType.Double, TBL_PROSPECT_TMP.OMZET_12);
            db.AddInParameter(cmd, "@OMZET_AVERAGE", DbType.Double, TBL_PROSPECT_TMP.OMZET_AVERAGE);
            db.AddInParameter(cmd, "@IS_PIPELINE_KP", DbType.String, TBL_PROSPECT_TMP.IS_PIPELINE_KP);

            db.ExecuteNonQuery(cmd);
        }

        public int SaveProspect2(ProspectInfo TBL_PROSPECT_TMP)
        {
            int result = 0;
            try
            {

                Database db = DBConnection.CreateDatabase();

                DbCommand cmd = db.GetSqlStringCommand(@"
                                UPDATE TBL_PROSPECT_TMP
                                SET 
                                    PROSPECT_NO = @PROSPECT_NO
                                    ,UNIT_CODE = @UNIT_CODE
                                    ,SOURCE_CODE = @SOURCE_CODE
                                    ,AO_CODE = @AO_CODE
                                    ,FULL_NAME = @FULL_NAME
                                    ,KTP_NAME = @KTP_NAME
                                    ,KTP_NO = @KTP_NO
                                    ,DATE_OF_BIRTH = @DATE_OF_BIRTH
                                    ,PLACE_OF_BIRTH = @PLACE_OF_BIRTH
                                    ,MOTHER_NAME = @MOTHER_NAME
                                    ,MALE_FEMALE = @MALE_FEMALE
                                    ,MARITAL_STATUS = @MARITAL_STATUS
                                    ,SPOUSE_NAME = @SPOUSE_NAME
                                    ,SPOUSE_DATE_OF_BIRTH = @SPOUSE_DATE_OF_BIRTH
                                    ,NPWP_NO = @NPWP_NO
                                    ,KODYA_KAB_BUSINESS = @KODYA_KAB_BUSINESS
                                    ,KECAMATAN_BUSINESS = @KECAMATAN_BUSINESS
                                    ,KELURAHAN_BUSINESS = @KELURAHAN_BUSINESS
                                    ,ZIP_CODE_BUSINESS = @ZIP_CODE_BUSINESS
                                    ,ADDRESS_BUSINESS = @ADDRESS_BUSINESS
                                    ,RESIDENTIAL_STATUS = @RESIDENTIAL_STATUS
                                    ,RESIDENTIAL_PHONE = @RESIDENTIAL_PHONE
                                    ,MOBILE_PHONE = @MOBILE_PHONE
                                    ,OFFICE_STATUS = @OFFICE_STATUS
                                    ,OFFICE_PHONE = @OFFICE_PHONE
                                    ,YEAR_OF_STAY = @YEAR_OF_STAY
                                    ,JML_TANGGUNGAN = @JML_TANGGUNGAN
                                    ,JENIS_USAHA = @JENIS_USAHA
                                    ,LAMA_USAHA = @LAMA_USAHA
                                    ,OMSET_PER_HARI = @OMSET_PER_HARI
                                    ,ADA_REK_BANK = @ADA_REK_BANK
                                    ,RENCANA_PLAFON = @RENCANA_PLAFON
                                    ,SALES_CHANNEL = @SALES_CHANNEL
                                    ,PROSPECT_TYPE = @PROSPECT_TYPE
                                    ,FUNDING_LENDING = @FUNDING_LENDING
                                    ,RESTRUCT_COMPONENT = @RESTRUCT_COMPONENT
                                    ,PROGRESS_STATUS = @PROGRESS_STATUS
                                    ,CYCLE_STATUS = @CYCLE_STATUS
                                    ,INSTALLMENT = @INSTALLMENT
                                    ,FAP_DATE = @FAP_DATE
                                    ,NO_OF_CALLS = @NO_OF_CALLS
                                    ,IS_INTERESTED = @IS_INTERESTED
                                    ,NOT_INTERESTED_REASON = @NOT_INTERESTED_REASON
                                    ,NOT_INTERESTED_NOTE = @NOT_INTERESTED_NOTE
                                    ,KET_KENDALA = @KET_KENDALA
                                    ,SCM_NOTE = @SCM_NOTE
                                    ,PRODUCT_TYPE = @PRODUCT_TYPE
                                    ,CIF_NUMBER = @CIF_NUMBER
                                    ,KARAKTER = @KARAKTER
                                    ,REPUTASI_DEBITUR = @REPUTASI_DEBITUR
                                    ,RIWAYAT_DSP = @RIWAYAT_DSP
                                    ,TAKSIRAN_PLAFON = @TAKSIRAN_PLAFON
                                    ,TAKSIRAN_USAHA = @TAKSIRAN_USAHA
                                    ,IS_DOCUMENT_COMPLETED = @IS_DOCUMENT_COMPLETED
                                    ,UM_DECISION = @UM_DECISION
                                    ,UM_DECISION_NOTE = @UM_DECISION_NOTE
                                    ,UM_DECISION_DATE = @UM_DECISION_DATE
                                    ,LOAN_NUMBER = @LOAN_NUMBER
                                    ,PRODUCT_CODE = @PRODUCT_CODE
                                    ,CURRENT_OS = @CURRENT_OS
                                    ,CURRENT_INSTALLMENT = @CURRENT_INSTALLMENT
                                    ,OFFER_PLAFON = @OFFER_PLAFON
                                    ,OFFER_INSTALLMENT = @OFFER_INSTALLMENT
                                    ,OFFER_INTEREST = @OFFER_INTEREST
                                    ,OFFER_TENOR = @OFFER_TENOR
                                    ,OFFER_FRESH_MONEY = @OFFER_FRESH_MONEY
                                    ,PRIORITY = @PRIORITY
                                    ,PIPELINE_START_TIME = @PIPELINE_START_TIME
                                    ,PIPELINE_FINISH_TIME = @PIPELINE_FINISH_TIME
                                    ,PROSPECT_START_TIME = @PROSPECT_START_TIME
                                    ,PROSPECT_FINISH_TIME = @PROSPECT_FINISH_TIME
                                    ,BI_GOOD_UNTIL = @BI_GOOD_UNTIL
                                    ,IS_BI_CHECKING = @IS_BI_CHECKING
                                    ,IS_OFFLINE_ENTRY = @IS_OFFLINE_ENTRY
                                    ,ENTRY_BY = @ENTRY_BY
                                    ,ENTRY_DATE = @ENTRY_DATE
                                    ,UPDATE_BY = @UPDATE_BY
                                    ,UPDATE_DATE = @UPDATE_DATE
                                    ,IS_BY_UPLOAD = @IS_BY_UPLOAD
                                    ,UPLOAD_BY = @UPLOAD_BY
                                    ,UPLOAD_DATE = @UPLOAD_DATE
                                    ,IS_DELETED = @IS_DELETED
                                    ,DEBTOR_TYPE = @DEBTOR_TYPE
                                    ,GU_FUP = @GU_FUP
                                    ,GU_CLOSE_DEAL = @GU_CLOSE_DEAL
                                    ,IS_DEDUP = @IS_DEDUP
                                    ,CO_ASSIGNED = @CO_ASSIGNED
                                    ,CHANNELING = @CHANNELING
                                    ,OMZET_1 = @OMZET_1
                                    ,OMZET_2 = @OMZET_2
                                    ,OMZET_3 = @OMZET_3
                                    ,OMZET_4 = @OMZET_4
                                    ,OMZET_5 = @OMZET_5
                                    ,OMZET_6 = @OMZET_6
                                    ,OMZET_7 = @OMZET_7
                                    ,OMZET_8 = @OMZET_8
                                    ,OMZET_9 = @OMZET_9
                                    ,OMZET_10 = @OMZET_10
                                    ,OMZET_11 = @OMZET_11
                                    ,OMZET_12 = @OMZET_12
                                    ,OMZET_AVERAGE = @OMZET_AVERAGE
                                    ,IS_PIPELINE_KP = @IS_PIPELINE_KP
                                    ,[EKSTERNAL] = @EKSTERNAL
                                    ,REFERENSI = @REFERENSI
                                    ,RES_1 = @RES_1
                                    ,RES_2 = @RES_2
                                    ,RES_3 = @RES_3
                                    ,RES_4 = @RES_4
                                    ,RES_5 = @RES_5
                                    ,RES_6 = @RES_6
                                    ,RES_7 = @RES_7
                                    ,RES_8 = @RES_8
                                    ,RES_9 = @RES_9
                                    ,RES_10 = @RES_10
                                    ,RES_11 = @RES_11
                                    ,RES_12 = @RES_12
                                    ,RES_13 = @RES_13
                                    ,RES_14 = @RES_14
                                    ,RES_15 = @RES_15
                                    ,RES_16 = @RES_16
                                    ,RES_17 = @RES_17
                                    ,RES_18 = @RES_18
                                    ,RES_19 = @RES_19
                                    ,RES_20 = @RES_20
                                    ,RES_21 = @RES_21
                                    ,RES_22 = @RES_22
                                    ,RES_23 = @RES_23
                                    ,RES_24 = @RES_24
                                    ,RES_25 = @RES_25
                                    ,RES_26 = @RES_26
                                    ,RES_27 = @RES_27
                                    ,RES_28 = @RES_28
                                    ,RES_29 = @RES_29
                                    ,RES_30 = @RES_30
                                    ,RES_31 = @RES_31
                                    ,RES_32 = @RES_32
                                    ,RES_33 = @RES_33
                                    ,RES_34 = @RES_34
                                    ,RES_35 = @RES_35
                                    ,RES_36 = @RES_36
                                    ,RES_37 = @RES_37
                                    ,RES_38 = @RES_38
                                    ,RES_39 = @RES_39
                                    ,RES_40 = @RES_40
                                    ,RES_41 = @RES_41
                                    ,RES_42 = @RES_42
                                    ,RES_43 = @RES_43
                                    ,RES_44 = @RES_44
                                    ,RES_45 = @RES_45
                                    ,RES_46 = @RES_46
                                    ,RES_47 = @RES_47
                                    ,RES_48 = @RES_48
                                    ,RES_49 = @RES_49
                                    ,RES_50 = @RES_50
                                    ,RES_51 = @RES_51
                                    ,RES_52 = @RES_52
                                    ,RES_53 = @RES_53
                                    ,RES_54 = @RES_54
                                    ,RES_55 = @RES_55
                                    ,RES_56 = @RES_56
                                    ,RES_57 = @RES_57
                                    ,RES_58 = @RES_58
                                    ,RES_59 = @RES_59
                                    ,RES_60 = @RES_60
                                    ,RES_61 = @RES_61
                                    ,RES_62 = @RES_62
                                    ,RES_63 = @RES_63
                                    ,RES_64 = @RES_64
                                    ,RES_65 = @RES_65
                                    ,RES_66 = @RES_66
                                    ,RES_67 = @RES_67
                                    ,RES_68 = @RES_68
                                    ,RES_69 = @RES_69
                                    ,RES_70 = @RES_70
                                    ,RES_71 = @RES_71
                                    ,RES_72 = @RES_72
                                    ,RES_73 = @RES_73
                                    ,RES_74 = @RES_74
                                    ,RES_75 = @RES_75
                                    ,RES_76 = @RES_76
                                    ,RES_77 = @RES_77
                                    ,RES_78 = @RES_78
                                    ,RES_79 = @RES_79
                                    ,RES_80 = @RES_80
                                    ,RES_81 = @RES_81
                                    ,RES_82 = @RES_82
                                    ,RES_83 = @RES_83
                                    ,RES_84 = @RES_84
                                    ,RES_85 = @RES_85
                                    ,RES_86 = @RES_86
                                    ,RES_87 = @RES_87
                                    ,RES_88 = @RES_88
                                    ,RES_89 = @RES_89
                                    ,RES_90 = @RES_90
                                    ,RES_91 = @RES_91
                                    ,RES_92 = @RES_92
                                    ,RES_93 = @RES_93
                                    ,RES_94 = @RES_94
                                    ,RES_95 = @RES_95
                                    ,RES_96 = @RES_96
                                    ,RES_97 = @RES_97
                                    ,RES_98 = @RES_98
                                    ,RES_99 = @RES_99
                                    ,RES_100 = @RES_100
                                    ,NIP = @NIP
                                    ,OLD_NIP = @OLD_NIP
                                    ,OLD_AO_CODE = @OLD_AO_CODE
                                    ,ALTERNATE_FOR = @ALTERNATE_FOR
                                WHERE (PROSPECT_ID = @PROSPECT_ID);
                                IF @@ROWCOUNT = 0
                                INSERT INTO TBL_PROSPECT_TMP
                                (
                                    PROSPECT_ID
                                    ,PROSPECT_NO
                                    ,UNIT_CODE
                                    ,SOURCE_CODE
                                    ,AO_CODE
                                    ,FULL_NAME
                                    ,KTP_NAME
                                    ,KTP_NO
                                    ,DATE_OF_BIRTH
                                    ,PLACE_OF_BIRTH
                                    ,MOTHER_NAME
                                    ,MALE_FEMALE
                                    ,MARITAL_STATUS
                                    ,SPOUSE_NAME
                                    ,SPOUSE_DATE_OF_BIRTH
                                    ,NPWP_NO
                                    ,KODYA_KAB_BUSINESS
                                    ,KECAMATAN_BUSINESS
                                    ,KELURAHAN_BUSINESS
                                    ,ZIP_CODE_BUSINESS
                                    ,ADDRESS_BUSINESS
                                    ,RESIDENTIAL_STATUS
                                    ,RESIDENTIAL_PHONE
                                    ,MOBILE_PHONE
                                    ,OFFICE_STATUS
                                    ,OFFICE_PHONE
                                    ,YEAR_OF_STAY
                                    ,JML_TANGGUNGAN
                                    ,JENIS_USAHA
                                    ,LAMA_USAHA
                                    ,OMSET_PER_HARI
                                    ,ADA_REK_BANK
                                    ,RENCANA_PLAFON
                                    ,SALES_CHANNEL
                                    ,PROSPECT_TYPE
                                    ,FUNDING_LENDING
                                    ,RESTRUCT_COMPONENT
                                    ,PROGRESS_STATUS
                                    ,CYCLE_STATUS
                                    ,INSTALLMENT
                                    ,FAP_DATE
                                    ,NO_OF_CALLS
                                    ,IS_INTERESTED
                                    ,NOT_INTERESTED_REASON
                                    ,NOT_INTERESTED_NOTE
                                    ,KET_KENDALA
                                    ,SCM_NOTE
                                    ,PRODUCT_TYPE
                                    ,CIF_NUMBER
                                    ,KARAKTER
                                    ,REPUTASI_DEBITUR
                                    ,RIWAYAT_DSP
                                    ,TAKSIRAN_PLAFON
                                    ,TAKSIRAN_USAHA
                                    ,IS_DOCUMENT_COMPLETED
                                    ,UM_DECISION
                                    ,UM_DECISION_NOTE
                                    ,UM_DECISION_DATE
                                    ,LOAN_NUMBER
                                    ,PRODUCT_CODE
                                    ,CURRENT_OS
                                    ,CURRENT_INSTALLMENT
                                    ,OFFER_PLAFON
                                    ,OFFER_INSTALLMENT
                                    ,OFFER_INTEREST
                                    ,OFFER_TENOR
                                    ,OFFER_FRESH_MONEY
                                    ,PRIORITY
                                    ,PIPELINE_START_TIME
                                    ,PIPELINE_FINISH_TIME
                                    ,PROSPECT_START_TIME
                                    ,PROSPECT_FINISH_TIME
                                    ,BI_GOOD_UNTIL
                                    ,IS_BI_CHECKING
                                    ,IS_OFFLINE_ENTRY
                                    ,ENTRY_BY
                                    ,ENTRY_DATE
                                    ,UPDATE_BY
                                    ,UPDATE_DATE
                                    ,IS_BY_UPLOAD
                                    ,UPLOAD_BY
                                    ,UPLOAD_DATE
                                    ,IS_DELETED
                                    ,DEBTOR_TYPE
                                    ,GU_FUP
                                    ,GU_CLOSE_DEAL
                                    ,IS_DEDUP
                                    ,CO_ASSIGNED
                                    ,CHANNELING
                                    ,OMZET_1
                                    ,OMZET_2
                                    ,OMZET_3
                                    ,OMZET_4
                                    ,OMZET_5
                                    ,OMZET_6
                                    ,OMZET_7
                                    ,OMZET_8
                                    ,OMZET_9
                                    ,OMZET_10
                                    ,OMZET_11
                                    ,OMZET_12
                                    ,OMZET_AVERAGE
                                    ,IS_PIPELINE_KP
                                    ,[EKSTERNAL]
                                    ,REFERENSI
                                    ,RES_1
                                    ,RES_2
                                    ,RES_3
                                    ,RES_4
                                    ,RES_5
                                    ,RES_6
                                    ,RES_7
                                    ,RES_8
                                    ,RES_9
                                    ,RES_10
                                    ,RES_11
                                    ,RES_12
                                    ,RES_13
                                    ,RES_14
                                    ,RES_15
                                    ,RES_16
                                    ,RES_17
                                    ,RES_18
                                    ,RES_19
                                    ,RES_20
                                    ,RES_21
                                    ,RES_22
                                    ,RES_23
                                    ,RES_24
                                    ,RES_25
                                    ,RES_26
                                    ,RES_27
                                    ,RES_28
                                    ,RES_29
                                    ,RES_30
                                    ,RES_31
                                    ,RES_32
                                    ,RES_33
                                    ,RES_34
                                    ,RES_35
                                    ,RES_36
                                    ,RES_37
                                    ,RES_38
                                    ,RES_39
                                    ,RES_40
                                    ,RES_41
                                    ,RES_42
                                    ,RES_43
                                    ,RES_44
                                    ,RES_45
                                    ,RES_46
                                    ,RES_47
                                    ,RES_48
                                    ,RES_49
                                    ,RES_50
                                    ,RES_51
                                    ,RES_52
                                    ,RES_53
                                    ,RES_54
                                    ,RES_55
                                    ,RES_56
                                    ,RES_57
                                    ,RES_58
                                    ,RES_59
                                    ,RES_60
                                    ,RES_61
                                    ,RES_62
                                    ,RES_63
                                    ,RES_64
                                    ,RES_65
                                    ,RES_66
                                    ,RES_67
                                    ,RES_68
                                    ,RES_69
                                    ,RES_70
                                    ,RES_71
                                    ,RES_72
                                    ,RES_73
                                    ,RES_74
                                    ,RES_75
                                    ,RES_76
                                    ,RES_77
                                    ,RES_78
                                    ,RES_79
                                    ,RES_80
                                    ,RES_81
                                    ,RES_82
                                    ,RES_83
                                    ,RES_84
                                    ,RES_85
                                    ,RES_86
                                    ,RES_87
                                    ,RES_88
                                    ,RES_89
                                    ,RES_90
                                    ,RES_91
                                    ,RES_92
                                    ,RES_93
                                    ,RES_94
                                    ,RES_95
                                    ,RES_96
                                    ,RES_97
                                    ,RES_98
                                    ,RES_99
                                    ,RES_100
                                    ,NIP 
                                    ,OLD_NIP
                                    ,OLD_AO_CODE
                                    ,ALTERNATE_FOR
                                ) 
                                VALUES
                                (
                                    @PROSPECT_ID
                                    ,@PROSPECT_NO
                                    ,@UNIT_CODE
                                    ,@SOURCE_CODE
                                    ,@AO_CODE
                                    ,@FULL_NAME
                                    ,@KTP_NAME
                                    ,@KTP_NO
                                    ,@DATE_OF_BIRTH
                                    ,@PLACE_OF_BIRTH
                                    ,@MOTHER_NAME
                                    ,@MALE_FEMALE
                                    ,@MARITAL_STATUS
                                    ,@SPOUSE_NAME
                                    ,@SPOUSE_DATE_OF_BIRTH
                                    ,@NPWP_NO
                                    ,@KODYA_KAB_BUSINESS
                                    ,@KECAMATAN_BUSINESS
                                    ,@KELURAHAN_BUSINESS
                                    ,@ZIP_CODE_BUSINESS
                                    ,@ADDRESS_BUSINESS
                                    ,@RESIDENTIAL_STATUS
                                    ,@RESIDENTIAL_PHONE
                                    ,@MOBILE_PHONE
                                    ,@OFFICE_STATUS
                                    ,@OFFICE_PHONE
                                    ,@YEAR_OF_STAY
                                    ,@JML_TANGGUNGAN
                                    ,@JENIS_USAHA
                                    ,@LAMA_USAHA
                                    ,@OMSET_PER_HARI
                                    ,@ADA_REK_BANK
                                    ,@RENCANA_PLAFON
                                    ,@SALES_CHANNEL
                                    ,@PROSPECT_TYPE
                                    ,@FUNDING_LENDING
                                    ,@RESTRUCT_COMPONENT
                                    ,@PROGRESS_STATUS
                                    ,@CYCLE_STATUS
                                    ,@INSTALLMENT
                                    ,@FAP_DATE
                                    ,@NO_OF_CALLS
                                    ,@IS_INTERESTED
                                    ,@NOT_INTERESTED_REASON
                                    ,@NOT_INTERESTED_NOTE
                                    ,@KET_KENDALA
                                    ,@SCM_NOTE
                                    ,@PRODUCT_TYPE
                                    ,@CIF_NUMBER
                                    ,@KARAKTER
                                    ,@REPUTASI_DEBITUR
                                    ,@RIWAYAT_DSP
                                    ,@TAKSIRAN_PLAFON
                                    ,@TAKSIRAN_USAHA
                                    ,@IS_DOCUMENT_COMPLETED
                                    ,@UM_DECISION
                                    ,@UM_DECISION_NOTE
                                    ,@UM_DECISION_DATE
                                    ,@LOAN_NUMBER
                                    ,@PRODUCT_CODE
                                    ,@CURRENT_OS
                                    ,@CURRENT_INSTALLMENT
                                    ,@OFFER_PLAFON
                                    ,@OFFER_INSTALLMENT
                                    ,@OFFER_INTEREST
                                    ,@OFFER_TENOR
                                    ,@OFFER_FRESH_MONEY
                                    ,@PRIORITY
                                    ,@PIPELINE_START_TIME
                                    ,@PIPELINE_FINISH_TIME
                                    ,@PROSPECT_START_TIME
                                    ,@PROSPECT_FINISH_TIME
                                    ,@BI_GOOD_UNTIL
                                    ,@IS_BI_CHECKING
                                    ,@IS_OFFLINE_ENTRY
                                    ,@ENTRY_BY
                                    ,@ENTRY_DATE
                                    ,@UPDATE_BY
                                    ,@UPDATE_DATE
                                    ,@IS_BY_UPLOAD
                                    ,@UPLOAD_BY
                                    ,@UPLOAD_DATE
                                    ,@IS_DELETED
                                    ,@DEBTOR_TYPE
                                    ,@GU_FUP
                                    ,@GU_CLOSE_DEAL
                                    ,@IS_DEDUP
                                    ,@CO_ASSIGNED
                                    ,@CHANNELING
                                    ,@OMZET_1
                                    ,@OMZET_2
                                    ,@OMZET_3
                                    ,@OMZET_4
                                    ,@OMZET_5
                                    ,@OMZET_6
                                    ,@OMZET_7
                                    ,@OMZET_8
                                    ,@OMZET_9
                                    ,@OMZET_10
                                    ,@OMZET_11
                                    ,@OMZET_12
                                    ,@OMZET_AVERAGE
                                    ,@IS_PIPELINE_KP
                                    ,@EKSTERNAL
                                    ,@REFERENSI
                                    ,@RES_1
                                    ,@RES_2
                                    ,@RES_3
                                    ,@RES_4
                                    ,@RES_5
                                    ,@RES_6
                                    ,@RES_7
                                    ,@RES_8
                                    ,@RES_9
                                    ,@RES_10
                                    ,@RES_11
                                    ,@RES_12
                                    ,@RES_13
                                    ,@RES_14
                                    ,@RES_15
                                    ,@RES_16
                                    ,@RES_17
                                    ,@RES_18
                                    ,@RES_19
                                    ,@RES_20
                                    ,@RES_21
                                    ,@RES_22
                                    ,@RES_23
                                    ,@RES_24
                                    ,@RES_25
                                    ,@RES_26
                                    ,@RES_27
                                    ,@RES_28
                                    ,@RES_29
                                    ,@RES_30
                                    ,@RES_31
                                    ,@RES_32
                                    ,@RES_33
                                    ,@RES_34
                                    ,@RES_35
                                    ,@RES_36
                                    ,@RES_37
                                    ,@RES_38
                                    ,@RES_39
                                    ,@RES_40
                                    ,@RES_41
                                    ,@RES_42
                                    ,@RES_43
                                    ,@RES_44
                                    ,@RES_45
                                    ,@RES_46
                                    ,@RES_47
                                    ,@RES_48
                                    ,@RES_49
                                    ,@RES_50
                                    ,@RES_51
                                    ,@RES_52
                                    ,@RES_53
                                    ,@RES_54
                                    ,@RES_55
                                    ,@RES_56
                                    ,@RES_57
                                    ,@RES_58
                                    ,@RES_59
                                    ,@RES_60
                                    ,@RES_61
                                    ,@RES_62
                                    ,@RES_63
                                    ,@RES_64
                                    ,@RES_65
                                    ,@RES_66
                                    ,@RES_67
                                    ,@RES_68
                                    ,@RES_69
                                    ,@RES_70
                                    ,@RES_71
                                    ,@RES_72
                                    ,@RES_73
                                    ,@RES_74
                                    ,@RES_75
                                    ,@RES_76
                                    ,@RES_77
                                    ,@RES_78
                                    ,@RES_79
                                    ,@RES_80
                                    ,@RES_81
                                    ,@RES_82
                                    ,@RES_83
                                    ,@RES_84
                                    ,@RES_85
                                    ,@RES_86
                                    ,@RES_87
                                    ,@RES_88
                                    ,@RES_89
                                    ,@RES_90
                                    ,@RES_91
                                    ,@RES_92
                                    ,@RES_93
                                    ,@RES_94
                                    ,@RES_95
                                    ,@RES_96
                                    ,@RES_97
                                    ,@RES_98
                                    ,@RES_99
                                    ,@RES_100
                                    ,@NIP 
                                    ,@OLD_NIP
                                    ,@OLD_AO_CODE
                                    ,@ALTERNATE_FOR
                                );
                            ");
                db.AddInParameter(cmd, "@PROSPECT_ID", DbType.String, TBL_PROSPECT_TMP.PROSPECT_ID);
                db.AddInParameter(cmd, "@PROSPECT_NO", DbType.String, TBL_PROSPECT_TMP.PROSPECT_NO);
                db.AddInParameter(cmd, "@UNIT_CODE", DbType.String, TBL_PROSPECT_TMP.UNIT_CODE);
                db.AddInParameter(cmd, "@SOURCE_CODE", DbType.String, TBL_PROSPECT_TMP.SOURCE_CODE);
                db.AddInParameter(cmd, "@AO_CODE", DbType.String, TBL_PROSPECT_TMP.AO_CODE);
                db.AddInParameter(cmd, "@FULL_NAME", DbType.String, TBL_PROSPECT_TMP.FULL_NAME);
                db.AddInParameter(cmd, "@KTP_NAME", DbType.String, TBL_PROSPECT_TMP.KTP_NAME);
                db.AddInParameter(cmd, "@KTP_NO", DbType.String, TBL_PROSPECT_TMP.KTP_NO);
                db.AddInParameter(cmd, "@DATE_OF_BIRTH", DbType.DateTime, TBL_PROSPECT_TMP.DATE_OF_BIRTH);
                db.AddInParameter(cmd, "@PLACE_OF_BIRTH", DbType.String, TBL_PROSPECT_TMP.PLACE_OF_BIRTH);
                db.AddInParameter(cmd, "@MOTHER_NAME", DbType.String, TBL_PROSPECT_TMP.MOTHER_NAME);
                db.AddInParameter(cmd, "@MALE_FEMALE", DbType.String, TBL_PROSPECT_TMP.MALE_FEMALE);
                db.AddInParameter(cmd, "@MARITAL_STATUS", DbType.String, TBL_PROSPECT_TMP.MARITAL_STATUS);
                db.AddInParameter(cmd, "@SPOUSE_NAME", DbType.String, TBL_PROSPECT_TMP.SPOUSE_NAME);
                db.AddInParameter(cmd, "@SPOUSE_DATE_OF_BIRTH", DbType.DateTime, TBL_PROSPECT_TMP.SPOUSE_DATE_OF_BIRTH);
                db.AddInParameter(cmd, "@NPWP_NO", DbType.String, TBL_PROSPECT_TMP.NPWP_NO);
                db.AddInParameter(cmd, "@KODYA_KAB_BUSINESS", DbType.String, TBL_PROSPECT_TMP.KODYA_KAB_BUSINESS);
                db.AddInParameter(cmd, "@KECAMATAN_BUSINESS", DbType.String, TBL_PROSPECT_TMP.KECAMATAN_BUSINESS);
                db.AddInParameter(cmd, "@KELURAHAN_BUSINESS", DbType.String, TBL_PROSPECT_TMP.KELURAHAN_BUSINESS);
                db.AddInParameter(cmd, "@ZIP_CODE_BUSINESS", DbType.String, TBL_PROSPECT_TMP.ZIP_CODE_BUSINESS);
                db.AddInParameter(cmd, "@ADDRESS_BUSINESS", DbType.String, TBL_PROSPECT_TMP.ADDRESS_BUSINESS);
                db.AddInParameter(cmd, "@RESIDENTIAL_STATUS", DbType.String, TBL_PROSPECT_TMP.RESIDENTIAL_STATUS);
                db.AddInParameter(cmd, "@RESIDENTIAL_PHONE", DbType.String, TBL_PROSPECT_TMP.RESIDENTIAL_PHONE);
                db.AddInParameter(cmd, "@MOBILE_PHONE", DbType.String, TBL_PROSPECT_TMP.MOBILE_PHONE);
                db.AddInParameter(cmd, "@OFFICE_STATUS", DbType.String, TBL_PROSPECT_TMP.OFFICE_STATUS);
                db.AddInParameter(cmd, "@OFFICE_PHONE", DbType.String, TBL_PROSPECT_TMP.OFFICE_PHONE);
                db.AddInParameter(cmd, "@YEAR_OF_STAY", DbType.Int16, TBL_PROSPECT_TMP.YEAR_OF_STAY);
                db.AddInParameter(cmd, "@JML_TANGGUNGAN", DbType.Int16, TBL_PROSPECT_TMP.JML_TANGGUNGAN);
                db.AddInParameter(cmd, "@JENIS_USAHA", DbType.String, TBL_PROSPECT_TMP.JENIS_USAHA);
                db.AddInParameter(cmd, "@LAMA_USAHA", DbType.String, TBL_PROSPECT_TMP.LAMA_USAHA);
                db.AddInParameter(cmd, "@OMSET_PER_HARI", DbType.Int32, TBL_PROSPECT_TMP.OMSET_PER_HARI);
                db.AddInParameter(cmd, "@ADA_REK_BANK", DbType.String, TBL_PROSPECT_TMP.ADA_REK_BANK);
                db.AddInParameter(cmd, "@RENCANA_PLAFON", DbType.Double, TBL_PROSPECT_TMP.RENCANA_PLAFON);
                db.AddInParameter(cmd, "@SALES_CHANNEL", DbType.String, TBL_PROSPECT_TMP.SALES_CHANNEL);
                db.AddInParameter(cmd, "@PROSPECT_TYPE", DbType.String, TBL_PROSPECT_TMP.PROSPECT_TYPE);
                db.AddInParameter(cmd, "@FUNDING_LENDING", DbType.String, TBL_PROSPECT_TMP.FUNDING_LENDING);
                db.AddInParameter(cmd, "@RESTRUCT_COMPONENT", DbType.String, TBL_PROSPECT_TMP.RESTRUCT_COMPONENT);
                db.AddInParameter(cmd, "@PROGRESS_STATUS", DbType.String, TBL_PROSPECT_TMP.PROGRESS_STATUS);
                db.AddInParameter(cmd, "@CYCLE_STATUS", DbType.String, TBL_PROSPECT_TMP.CYCLE_STATUS);
                db.AddInParameter(cmd, "@INSTALLMENT", DbType.Double, TBL_PROSPECT_TMP.INSTALLMENT);
                db.AddInParameter(cmd, "@FAP_DATE", DbType.DateTime, TBL_PROSPECT_TMP.FAP_DATE);
                db.AddInParameter(cmd, "@NO_OF_CALLS", DbType.Int16, TBL_PROSPECT_TMP.NO_OF_CALLS);
                db.AddInParameter(cmd, "@IS_INTERESTED", DbType.String, TBL_PROSPECT_TMP.IS_INTERESTED);
                db.AddInParameter(cmd, "@NOT_INTERESTED_REASON", DbType.String, TBL_PROSPECT_TMP.NOT_INTERESTED_REASON);
                db.AddInParameter(cmd, "@NOT_INTERESTED_NOTE", DbType.String, TBL_PROSPECT_TMP.NOT_INTERESTED_NOTE);
                db.AddInParameter(cmd, "@KET_KENDALA", DbType.String, TBL_PROSPECT_TMP.KET_KENDALA);
                db.AddInParameter(cmd, "@SCM_NOTE", DbType.String, TBL_PROSPECT_TMP.SCM_NOTE);
                db.AddInParameter(cmd, "@PRODUCT_TYPE", DbType.Int16, TBL_PROSPECT_TMP.PRODUCT_TYPE);
                db.AddInParameter(cmd, "@CIF_NUMBER", DbType.Int32, TBL_PROSPECT_TMP.CIF_NUMBER);
                db.AddInParameter(cmd, "@KARAKTER", DbType.String, TBL_PROSPECT_TMP.KARAKTER);
                db.AddInParameter(cmd, "@REPUTASI_DEBITUR", DbType.String, TBL_PROSPECT_TMP.REPUTASI_DEBITUR);
                db.AddInParameter(cmd, "@RIWAYAT_DSP", DbType.String, TBL_PROSPECT_TMP.RIWAYAT_DSP);
                db.AddInParameter(cmd, "@TAKSIRAN_PLAFON", DbType.Double, TBL_PROSPECT_TMP.TAKSIRAN_PLAFON);
                db.AddInParameter(cmd, "@TAKSIRAN_USAHA", DbType.String, TBL_PROSPECT_TMP.TAKSIRAN_USAHA);
                db.AddInParameter(cmd, "@IS_DOCUMENT_COMPLETED", DbType.String, TBL_PROSPECT_TMP.IS_DOCUMENT_COMPLETED);
                db.AddInParameter(cmd, "@UM_DECISION", DbType.String, TBL_PROSPECT_TMP.UM_DECISION);
                db.AddInParameter(cmd, "@UM_DECISION_NOTE", DbType.String, TBL_PROSPECT_TMP.UM_DECISION_NOTE);
                db.AddInParameter(cmd, "@UM_DECISION_DATE", DbType.DateTime, TBL_PROSPECT_TMP.UM_DECISION_DATE);
                db.AddInParameter(cmd, "@LOAN_NUMBER", DbType.String, TBL_PROSPECT_TMP.LOAN_NUMBER);
                db.AddInParameter(cmd, "@PRODUCT_CODE", DbType.String, TBL_PROSPECT_TMP.PRODUCT_CODE);
                db.AddInParameter(cmd, "@CURRENT_OS", DbType.Double, TBL_PROSPECT_TMP.CURRENT_OS);
                db.AddInParameter(cmd, "@CURRENT_INSTALLMENT", DbType.Double, TBL_PROSPECT_TMP.CURRENT_INSTALLMENT);
                db.AddInParameter(cmd, "@OFFER_PLAFON", DbType.Double, TBL_PROSPECT_TMP.OFFER_PLAFON);
                db.AddInParameter(cmd, "@OFFER_INSTALLMENT", DbType.Double, TBL_PROSPECT_TMP.OFFER_INSTALLMENT);
                db.AddInParameter(cmd, "@OFFER_INTEREST", DbType.String, TBL_PROSPECT_TMP.OFFER_INTEREST);
                db.AddInParameter(cmd, "@OFFER_TENOR", DbType.Int16, TBL_PROSPECT_TMP.OFFER_TENOR);
                db.AddInParameter(cmd, "@OFFER_FRESH_MONEY", DbType.Double, TBL_PROSPECT_TMP.OFFER_FRESH_MONEY);
                db.AddInParameter(cmd, "@PRIORITY", DbType.String, TBL_PROSPECT_TMP.PRIORITY);
                db.AddInParameter(cmd, "@PIPELINE_START_TIME", DbType.DateTime, TBL_PROSPECT_TMP.PIPELINE_START_TIME);
                db.AddInParameter(cmd, "@PIPELINE_FINISH_TIME", DbType.DateTime, TBL_PROSPECT_TMP.PIPELINE_FINISH_TIME);
                db.AddInParameter(cmd, "@PROSPECT_START_TIME", DbType.DateTime, TBL_PROSPECT_TMP.PROSPECT_START_TIME);
                db.AddInParameter(cmd, "@PROSPECT_FINISH_TIME", DbType.DateTime, TBL_PROSPECT_TMP.PROSPECT_FINISH_TIME);
                db.AddInParameter(cmd, "@BI_GOOD_UNTIL", DbType.DateTime, TBL_PROSPECT_TMP.BI_GOOD_UNTIL);
                db.AddInParameter(cmd, "@IS_BI_CHECKING", DbType.String, TBL_PROSPECT_TMP.IS_BI_CHECKING);
                db.AddInParameter(cmd, "@IS_OFFLINE_ENTRY", DbType.String, TBL_PROSPECT_TMP.IS_OFFLINE_ENTRY);
                db.AddInParameter(cmd, "@ENTRY_BY", DbType.String, TBL_PROSPECT_TMP.ENTRY_BY);
                db.AddInParameter(cmd, "@ENTRY_DATE", DbType.DateTime, TBL_PROSPECT_TMP.ENTRY_DATE);
                db.AddInParameter(cmd, "@UPDATE_BY", DbType.String, TBL_PROSPECT_TMP.UPDATE_BY);
                db.AddInParameter(cmd, "@UPDATE_DATE", DbType.DateTime, TBL_PROSPECT_TMP.UPDATE_DATE);
                db.AddInParameter(cmd, "@IS_BY_UPLOAD", DbType.String, TBL_PROSPECT_TMP.IS_BY_UPLOAD);
                db.AddInParameter(cmd, "@UPLOAD_BY", DbType.String, TBL_PROSPECT_TMP.UPLOAD_BY);
                db.AddInParameter(cmd, "@UPLOAD_DATE", DbType.DateTime, TBL_PROSPECT_TMP.UPLOAD_DATE);
                db.AddInParameter(cmd, "@IS_DELETED", DbType.String, TBL_PROSPECT_TMP.IS_DELETED);
                db.AddInParameter(cmd, "@DEBTOR_TYPE", DbType.String, TBL_PROSPECT_TMP.DEBTOR_TYPE);
                db.AddInParameter(cmd, "@GU_FUP", DbType.DateTime, TBL_PROSPECT_TMP.GU_FUP);
                db.AddInParameter(cmd, "@GU_CLOSE_DEAL", DbType.DateTime, TBL_PROSPECT_TMP.GU_CLOSE_DEAL);
                db.AddInParameter(cmd, "@IS_DEDUP", DbType.String, TBL_PROSPECT_TMP.IS_DEDUP);
                db.AddInParameter(cmd, "@CHANNELING", DbType.String, TBL_PROSPECT_TMP.CHANNELING);
                db.AddInParameter(cmd, "@OMZET_1", DbType.Double, TBL_PROSPECT_TMP.OMZET_1);
                db.AddInParameter(cmd, "@OMZET_2", DbType.Double, TBL_PROSPECT_TMP.OMZET_2);
                db.AddInParameter(cmd, "@OMZET_3", DbType.Double, TBL_PROSPECT_TMP.OMZET_3);
                db.AddInParameter(cmd, "@OMZET_4", DbType.Double, TBL_PROSPECT_TMP.OMZET_4);
                db.AddInParameter(cmd, "@OMZET_5", DbType.Double, TBL_PROSPECT_TMP.OMZET_5);
                db.AddInParameter(cmd, "@OMZET_6", DbType.Double, TBL_PROSPECT_TMP.OMZET_6);
                db.AddInParameter(cmd, "@OMZET_7", DbType.Double, TBL_PROSPECT_TMP.OMZET_7);
                db.AddInParameter(cmd, "@OMZET_8", DbType.Double, TBL_PROSPECT_TMP.OMZET_8);
                db.AddInParameter(cmd, "@OMZET_9", DbType.Double, TBL_PROSPECT_TMP.OMZET_9);
                db.AddInParameter(cmd, "@OMZET_10", DbType.Double, TBL_PROSPECT_TMP.OMZET_10);
                db.AddInParameter(cmd, "@OMZET_11", DbType.Double, TBL_PROSPECT_TMP.OMZET_11);
                db.AddInParameter(cmd, "@OMZET_12", DbType.Double, TBL_PROSPECT_TMP.OMZET_12);
                db.AddInParameter(cmd, "@OMZET_AVERAGE", DbType.Double, TBL_PROSPECT_TMP.OMZET_AVERAGE);
                db.AddInParameter(cmd, "@IS_PIPELINE_KP", DbType.String, TBL_PROSPECT_TMP.IS_PIPELINE_KP);
                db.AddInParameter(cmd, "@EKSTERNAL", DbType.String, TBL_PROSPECT_TMP.EKSTERNAL);
                db.AddInParameter(cmd, "@REFERENSI", DbType.String, TBL_PROSPECT_TMP.REFERENSI);
                db.AddInParameter(cmd, "@RES_1", DbType.String, TBL_PROSPECT_TMP.RES_1);
                db.AddInParameter(cmd, "@RES_2", DbType.String, TBL_PROSPECT_TMP.RES_2);
                db.AddInParameter(cmd, "@RES_3", DbType.String, TBL_PROSPECT_TMP.RES_3);
                db.AddInParameter(cmd, "@RES_4", DbType.String, TBL_PROSPECT_TMP.RES_4);
                db.AddInParameter(cmd, "@RES_5", DbType.String, TBL_PROSPECT_TMP.RES_5);
                db.AddInParameter(cmd, "@RES_6", DbType.String, TBL_PROSPECT_TMP.RES_6);
                db.AddInParameter(cmd, "@RES_7", DbType.String, TBL_PROSPECT_TMP.RES_7);
                db.AddInParameter(cmd, "@RES_8", DbType.String, TBL_PROSPECT_TMP.RES_8);
                db.AddInParameter(cmd, "@RES_9", DbType.String, TBL_PROSPECT_TMP.RES_9);
                db.AddInParameter(cmd, "@RES_10", DbType.String, TBL_PROSPECT_TMP.RES_10);
                db.AddInParameter(cmd, "@RES_11", DbType.String, TBL_PROSPECT_TMP.RES_11);
                db.AddInParameter(cmd, "@RES_12", DbType.String, TBL_PROSPECT_TMP.RES_12);
                db.AddInParameter(cmd, "@RES_13", DbType.String, TBL_PROSPECT_TMP.RES_13);
                db.AddInParameter(cmd, "@RES_14", DbType.String, TBL_PROSPECT_TMP.RES_14);
                db.AddInParameter(cmd, "@RES_15", DbType.String, TBL_PROSPECT_TMP.RES_15);
                db.AddInParameter(cmd, "@RES_16", DbType.String, TBL_PROSPECT_TMP.RES_16);
                db.AddInParameter(cmd, "@RES_17", DbType.String, TBL_PROSPECT_TMP.RES_17);
                db.AddInParameter(cmd, "@RES_18", DbType.String, TBL_PROSPECT_TMP.RES_18);
                db.AddInParameter(cmd, "@RES_19", DbType.String, TBL_PROSPECT_TMP.RES_19);
                db.AddInParameter(cmd, "@RES_20", DbType.String, TBL_PROSPECT_TMP.RES_20);
                db.AddInParameter(cmd, "@RES_21", DbType.String, TBL_PROSPECT_TMP.RES_21);
                db.AddInParameter(cmd, "@RES_22", DbType.String, TBL_PROSPECT_TMP.RES_22);
                db.AddInParameter(cmd, "@RES_23", DbType.String, TBL_PROSPECT_TMP.RES_23);
                db.AddInParameter(cmd, "@RES_24", DbType.String, TBL_PROSPECT_TMP.RES_24);
                db.AddInParameter(cmd, "@RES_25", DbType.String, TBL_PROSPECT_TMP.RES_25);
                db.AddInParameter(cmd, "@RES_26", DbType.DateTime, TBL_PROSPECT_TMP.RES_26);
                db.AddInParameter(cmd, "@RES_27", DbType.DateTime, TBL_PROSPECT_TMP.RES_27);
                db.AddInParameter(cmd, "@RES_28", DbType.DateTime, TBL_PROSPECT_TMP.RES_28);
                db.AddInParameter(cmd, "@RES_29", DbType.DateTime, TBL_PROSPECT_TMP.RES_29);
                db.AddInParameter(cmd, "@RES_30", DbType.DateTime, TBL_PROSPECT_TMP.RES_30);
                db.AddInParameter(cmd, "@RES_31", DbType.DateTime, TBL_PROSPECT_TMP.RES_31);
                db.AddInParameter(cmd, "@RES_32", DbType.DateTime, TBL_PROSPECT_TMP.RES_32);
                db.AddInParameter(cmd, "@RES_33", DbType.DateTime, TBL_PROSPECT_TMP.RES_33);
                db.AddInParameter(cmd, "@RES_34", DbType.DateTime, TBL_PROSPECT_TMP.RES_34);
                db.AddInParameter(cmd, "@RES_35", DbType.DateTime, TBL_PROSPECT_TMP.RES_35);
                db.AddInParameter(cmd, "@RES_36", DbType.DateTime, TBL_PROSPECT_TMP.RES_36);
                db.AddInParameter(cmd, "@RES_37", DbType.DateTime, TBL_PROSPECT_TMP.RES_37);
                db.AddInParameter(cmd, "@RES_38", DbType.DateTime, TBL_PROSPECT_TMP.RES_38);
                db.AddInParameter(cmd, "@RES_39", DbType.DateTime, TBL_PROSPECT_TMP.RES_39);
                db.AddInParameter(cmd, "@RES_40", DbType.DateTime, TBL_PROSPECT_TMP.RES_40);
                db.AddInParameter(cmd, "@RES_41", DbType.DateTime, TBL_PROSPECT_TMP.RES_41);
                db.AddInParameter(cmd, "@RES_42", DbType.DateTime, TBL_PROSPECT_TMP.RES_42);
                db.AddInParameter(cmd, "@RES_43", DbType.DateTime, TBL_PROSPECT_TMP.RES_43);
                db.AddInParameter(cmd, "@RES_44", DbType.DateTime, TBL_PROSPECT_TMP.RES_44);
                db.AddInParameter(cmd, "@RES_45", DbType.DateTime, TBL_PROSPECT_TMP.RES_45);
                db.AddInParameter(cmd, "@RES_46", DbType.DateTime, TBL_PROSPECT_TMP.RES_46);
                db.AddInParameter(cmd, "@RES_47", DbType.DateTime, TBL_PROSPECT_TMP.RES_47);
                db.AddInParameter(cmd, "@RES_48", DbType.DateTime, TBL_PROSPECT_TMP.RES_48);
                db.AddInParameter(cmd, "@RES_49", DbType.DateTime, TBL_PROSPECT_TMP.RES_49);
                db.AddInParameter(cmd, "@RES_50", DbType.DateTime, TBL_PROSPECT_TMP.RES_50);
                db.AddInParameter(cmd, "@RES_51", DbType.Double, TBL_PROSPECT_TMP.RES_51);
                db.AddInParameter(cmd, "@RES_52", DbType.Double, TBL_PROSPECT_TMP.RES_52);
                db.AddInParameter(cmd, "@RES_53", DbType.Double, TBL_PROSPECT_TMP.RES_53);
                db.AddInParameter(cmd, "@RES_54", DbType.Double, TBL_PROSPECT_TMP.RES_54);
                db.AddInParameter(cmd, "@RES_55", DbType.Double, TBL_PROSPECT_TMP.RES_55);
                db.AddInParameter(cmd, "@RES_56", DbType.Double, TBL_PROSPECT_TMP.RES_56);
                db.AddInParameter(cmd, "@RES_57", DbType.Double, TBL_PROSPECT_TMP.RES_57);
                db.AddInParameter(cmd, "@RES_58", DbType.Double, TBL_PROSPECT_TMP.RES_58);
                db.AddInParameter(cmd, "@RES_59", DbType.Double, TBL_PROSPECT_TMP.RES_59);
                db.AddInParameter(cmd, "@RES_60", DbType.Double, TBL_PROSPECT_TMP.RES_60);
                db.AddInParameter(cmd, "@RES_61", DbType.Double, TBL_PROSPECT_TMP.RES_61);
                db.AddInParameter(cmd, "@RES_62", DbType.Double, TBL_PROSPECT_TMP.RES_62);
                db.AddInParameter(cmd, "@RES_63", DbType.Double, TBL_PROSPECT_TMP.RES_63);
                db.AddInParameter(cmd, "@RES_64", DbType.Double, TBL_PROSPECT_TMP.RES_64);
                db.AddInParameter(cmd, "@RES_65", DbType.Double, TBL_PROSPECT_TMP.RES_65);
                db.AddInParameter(cmd, "@RES_66", DbType.Double, TBL_PROSPECT_TMP.RES_66);
                db.AddInParameter(cmd, "@RES_67", DbType.Double, TBL_PROSPECT_TMP.RES_67);
                db.AddInParameter(cmd, "@RES_68", DbType.Double, TBL_PROSPECT_TMP.RES_68);
                db.AddInParameter(cmd, "@RES_69", DbType.Double, TBL_PROSPECT_TMP.RES_69);
                db.AddInParameter(cmd, "@RES_70", DbType.Double, TBL_PROSPECT_TMP.RES_70);
                db.AddInParameter(cmd, "@RES_71", DbType.Double, TBL_PROSPECT_TMP.RES_71);
                db.AddInParameter(cmd, "@RES_72", DbType.Double, TBL_PROSPECT_TMP.RES_72);
                db.AddInParameter(cmd, "@RES_73", DbType.Double, TBL_PROSPECT_TMP.RES_73);
                db.AddInParameter(cmd, "@RES_74", DbType.Double, TBL_PROSPECT_TMP.RES_74);
                db.AddInParameter(cmd, "@RES_75", DbType.Double, TBL_PROSPECT_TMP.RES_75);
                db.AddInParameter(cmd, "@RES_76", DbType.String, TBL_PROSPECT_TMP.RES_76);
                db.AddInParameter(cmd, "@RES_77", DbType.String, TBL_PROSPECT_TMP.RES_77);
                db.AddInParameter(cmd, "@RES_78", DbType.String, TBL_PROSPECT_TMP.RES_78);
                db.AddInParameter(cmd, "@RES_79", DbType.String, TBL_PROSPECT_TMP.RES_79);
                db.AddInParameter(cmd, "@RES_80", DbType.String, TBL_PROSPECT_TMP.RES_80);
                db.AddInParameter(cmd, "@RES_81", DbType.String, TBL_PROSPECT_TMP.RES_81);
                db.AddInParameter(cmd, "@RES_82", DbType.String, TBL_PROSPECT_TMP.RES_82);
                db.AddInParameter(cmd, "@RES_83", DbType.String, TBL_PROSPECT_TMP.RES_83);
                db.AddInParameter(cmd, "@RES_84", DbType.String, TBL_PROSPECT_TMP.RES_84);
                db.AddInParameter(cmd, "@RES_85", DbType.String, TBL_PROSPECT_TMP.RES_85);
                db.AddInParameter(cmd, "@RES_86", DbType.String, TBL_PROSPECT_TMP.RES_86);
                db.AddInParameter(cmd, "@RES_87", DbType.String, TBL_PROSPECT_TMP.RES_87);
                db.AddInParameter(cmd, "@RES_88", DbType.String, TBL_PROSPECT_TMP.RES_88);
                db.AddInParameter(cmd, "@RES_89", DbType.String, TBL_PROSPECT_TMP.RES_89);
                db.AddInParameter(cmd, "@RES_90", DbType.String, TBL_PROSPECT_TMP.RES_90);
                db.AddInParameter(cmd, "@RES_91", DbType.String, TBL_PROSPECT_TMP.RES_91);
                db.AddInParameter(cmd, "@RES_92", DbType.String, TBL_PROSPECT_TMP.RES_92);
                db.AddInParameter(cmd, "@RES_93", DbType.String, TBL_PROSPECT_TMP.RES_93);
                db.AddInParameter(cmd, "@RES_94", DbType.String, TBL_PROSPECT_TMP.RES_94);
                db.AddInParameter(cmd, "@RES_95", DbType.String, TBL_PROSPECT_TMP.RES_95);
                db.AddInParameter(cmd, "@RES_96", DbType.String, TBL_PROSPECT_TMP.RES_96);
                db.AddInParameter(cmd, "@RES_97", DbType.String, TBL_PROSPECT_TMP.RES_97);
                db.AddInParameter(cmd, "@RES_98", DbType.String, TBL_PROSPECT_TMP.RES_98);
                db.AddInParameter(cmd, "@RES_99", DbType.String, TBL_PROSPECT_TMP.RES_99);
                db.AddInParameter(cmd, "@RES_100", DbType.String, TBL_PROSPECT_TMP.RES_100);
                db.AddInParameter(cmd, "@NIP", DbType.String, TBL_PROSPECT_TMP.NIP);
                db.AddInParameter(cmd, "@OLD_NIP", DbType.String, TBL_PROSPECT_TMP.OLD_NIP);
                db.AddInParameter(cmd, "@OLD_AO_CODE", DbType.String, TBL_PROSPECT_TMP.OLD_AO_CODE);
                db.AddInParameter(cmd, "@ALTERNATE_FOR", DbType.String, TBL_PROSPECT_TMP.ALTERNATE_FOR);

                result = db.ExecuteNonQuery(cmd);
            }
            catch (Exception) { }
            return result;
        }

        public void SaveProspectAddress(ProspectAddressInfo TBL_PROSPECT_ADDRESS_TMP)
        {
            Database db = DBConnection.CreateDatabase();

            DbCommand cmd = db.GetSqlStringCommand(@"
                                UPDATE TBL_PROSPECT_ADDRESS_TMP 
                                SET 
                                    KODYA_KAB = @KODYA_KAB
                                    ,KECAMATAN = @KECAMATAN
                                    ,KELURAHAN = @KELURAHAN
                                    ,ZIP_CODE = @ZIP_CODE
                                    ,ADDRESS = @ADDRESS
                                    ,ADDRESS_RT = @ADDRESS_RT
                                    ,ADDRESS_RW = @ADDRESS_RW
                                    ,ENTRY_BY = @ENTRY_BY
                                    ,ENTRY_DATE = @ENTRY_DATE
                                    ,UPDATE_BY = @UPDATE_BY
                                    ,UPDATE_DATE = @UPDATE_DATE
                                    ,UPLOAD_BY = @UPLOAD_BY
                                    ,UPLOAD_DATE = @UPLOAD_DATE
                                WHERE (PROSPECT_ID = @PROSPECT_ID) AND (ADDRESS_TYPE = @ADDRESS_TYPE);
                                IF @@ROWCOUNT = 0
                                INSERT INTO TBL_PROSPECT_ADDRESS_TMP
                                (
                                    PROSPECT_ID
                                    ,ADDRESS_TYPE
                                    ,KODYA_KAB
                                    ,KECAMATAN
                                    ,KELURAHAN
                                    ,ZIP_CODE
                                    ,ADDRESS
                                    ,ADDRESS_RT
                                    ,ADDRESS_RW
                                    ,ENTRY_BY
                                    ,ENTRY_DATE
                                    ,UPDATE_BY
                                    ,UPDATE_DATE
                                    ,UPLOAD_BY
                                    ,UPLOAD_DATE
                                ) 
                                VALUES
                                (
                                    @PROSPECT_ID
                                    ,@ADDRESS_TYPE
                                    ,@KODYA_KAB
                                    ,@KECAMATAN
                                    ,@KELURAHAN
                                    ,@ZIP_CODE
                                    ,@ADDRESS
                                    ,@ADDRESS_RT
                                    ,@ADDRESS_RW
                                    ,@ENTRY_BY
                                    ,@ENTRY_DATE
                                    ,@UPDATE_BY
                                    ,@UPDATE_DATE
                                    ,@UPLOAD_BY
                                    ,@UPLOAD_DATE
                                );
                              ");
            db.AddInParameter(cmd, "@PROSPECT_ID", DbType.String, TBL_PROSPECT_ADDRESS_TMP.PROSPECT_ID);
            db.AddInParameter(cmd, "@ADDRESS_TYPE", DbType.String, TBL_PROSPECT_ADDRESS_TMP.ADDRESS_TYPE);
            db.AddInParameter(cmd, "@KODYA_KAB", DbType.String, TBL_PROSPECT_ADDRESS_TMP.KODYA_KAB);
            db.AddInParameter(cmd, "@KECAMATAN", DbType.String, TBL_PROSPECT_ADDRESS_TMP.KECAMATAN);
            db.AddInParameter(cmd, "@KELURAHAN", DbType.String, TBL_PROSPECT_ADDRESS_TMP.KELURAHAN);
            db.AddInParameter(cmd, "@ZIP_CODE", DbType.String, TBL_PROSPECT_ADDRESS_TMP.ZIP_CODE);
            db.AddInParameter(cmd, "@ADDRESS", DbType.String, TBL_PROSPECT_ADDRESS_TMP.ADDRESS);
            db.AddInParameter(cmd, "@ADDRESS_RT", DbType.String, TBL_PROSPECT_ADDRESS_TMP.ADDRESS_RT);
            db.AddInParameter(cmd, "@ADDRESS_RW", DbType.String, TBL_PROSPECT_ADDRESS_TMP.ADDRESS_RW);
            db.AddInParameter(cmd, "@ENTRY_BY", DbType.String, TBL_PROSPECT_ADDRESS_TMP.ENTRY_BY);
            db.AddInParameter(cmd, "@ENTRY_DATE", DbType.DateTime, TBL_PROSPECT_ADDRESS_TMP.ENTRY_DATE);
            db.AddInParameter(cmd, "@UPDATE_BY", DbType.String, TBL_PROSPECT_ADDRESS_TMP.UPDATE_BY);
            db.AddInParameter(cmd, "@UPDATE_DATE", DbType.DateTime, TBL_PROSPECT_ADDRESS_TMP.UPDATE_DATE);
            db.AddInParameter(cmd, "@UPLOAD_BY", DbType.String, TBL_PROSPECT_ADDRESS_TMP.UPLOAD_BY);
            db.AddInParameter(cmd, "@UPLOAD_DATE", DbType.DateTime, TBL_PROSPECT_ADDRESS_TMP.UPLOAD_DATE);
            db.ExecuteNonQuery(cmd);
        }

        public int SaveProspectAddress2(ProspectAddressInfo TBL_PROSPECT_ADDRESS_TMP)
        {
            int result = 0;
            try
            {
                Database db = DBConnection.CreateDatabase();

                DbCommand cmd = db.GetSqlStringCommand(@"
                                UPDATE TBL_PROSPECT_ADDRESS_TMP 
                                SET 
                                    KODYA_KAB = @KODYA_KAB
                                    ,KECAMATAN = @KECAMATAN
                                    ,KELURAHAN = @KELURAHAN
                                    ,ZIP_CODE = @ZIP_CODE
                                    ,ADDRESS = @ADDRESS
                                    ,ADDRESS_RT = @ADDRESS_RT
                                    ,ADDRESS_RW = @ADDRESS_RW
                                    ,ENTRY_BY = @ENTRY_BY
                                    ,ENTRY_DATE = @ENTRY_DATE
                                    ,UPDATE_BY = @UPDATE_BY
                                    ,UPDATE_DATE = @UPDATE_DATE
                                    ,UPLOAD_BY = @UPLOAD_BY
                                    ,UPLOAD_DATE = @UPLOAD_DATE
                                WHERE (PROSPECT_ID = @PROSPECT_ID) AND (ADDRESS_TYPE = @ADDRESS_TYPE);
                                IF @@ROWCOUNT = 0
                                INSERT INTO TBL_PROSPECT_ADDRESS_TMP
                                (
                                    PROSPECT_ID
                                    ,ADDRESS_TYPE
                                    ,KODYA_KAB
                                    ,KECAMATAN
                                    ,KELURAHAN
                                    ,ZIP_CODE
                                    ,ADDRESS
                                    ,ADDRESS_RT
                                    ,ADDRESS_RW
                                    ,ENTRY_BY
                                    ,ENTRY_DATE
                                    ,UPDATE_BY
                                    ,UPDATE_DATE
                                    ,UPLOAD_BY
                                    ,UPLOAD_DATE
                                ) 
                                VALUES
                                (
                                    @PROSPECT_ID
                                    ,@ADDRESS_TYPE
                                    ,@KODYA_KAB
                                    ,@KECAMATAN
                                    ,@KELURAHAN
                                    ,@ZIP_CODE
                                    ,@ADDRESS
                                    ,@ADDRESS_RT
                                    ,@ADDRESS_RW
                                    ,@ENTRY_BY
                                    ,@ENTRY_DATE
                                    ,@UPDATE_BY
                                    ,@UPDATE_DATE
                                    ,@UPLOAD_BY
                                    ,@UPLOAD_DATE
                                );
                              ");
                db.AddInParameter(cmd, "@PROSPECT_ID", DbType.String, TBL_PROSPECT_ADDRESS_TMP.PROSPECT_ID);
                db.AddInParameter(cmd, "@ADDRESS_TYPE", DbType.String, TBL_PROSPECT_ADDRESS_TMP.ADDRESS_TYPE);
                db.AddInParameter(cmd, "@KODYA_KAB", DbType.String, TBL_PROSPECT_ADDRESS_TMP.KODYA_KAB);
                db.AddInParameter(cmd, "@KECAMATAN", DbType.String, TBL_PROSPECT_ADDRESS_TMP.KECAMATAN);
                db.AddInParameter(cmd, "@KELURAHAN", DbType.String, TBL_PROSPECT_ADDRESS_TMP.KELURAHAN);
                db.AddInParameter(cmd, "@ZIP_CODE", DbType.String, TBL_PROSPECT_ADDRESS_TMP.ZIP_CODE);
                db.AddInParameter(cmd, "@ADDRESS", DbType.String, TBL_PROSPECT_ADDRESS_TMP.ADDRESS);
                db.AddInParameter(cmd, "@ADDRESS_RT", DbType.String, TBL_PROSPECT_ADDRESS_TMP.ADDRESS_RT);
                db.AddInParameter(cmd, "@ADDRESS_RW", DbType.String, TBL_PROSPECT_ADDRESS_TMP.ADDRESS_RW);
                db.AddInParameter(cmd, "@ENTRY_BY", DbType.String, TBL_PROSPECT_ADDRESS_TMP.ENTRY_BY);
                db.AddInParameter(cmd, "@ENTRY_DATE", DbType.DateTime, TBL_PROSPECT_ADDRESS_TMP.ENTRY_DATE);
                db.AddInParameter(cmd, "@UPDATE_BY", DbType.String, TBL_PROSPECT_ADDRESS_TMP.UPDATE_BY);
                db.AddInParameter(cmd, "@UPDATE_DATE", DbType.DateTime, TBL_PROSPECT_ADDRESS_TMP.UPDATE_DATE);
                db.AddInParameter(cmd, "@UPLOAD_BY", DbType.String, TBL_PROSPECT_ADDRESS_TMP.UPLOAD_BY);
                db.AddInParameter(cmd, "@UPLOAD_DATE", DbType.DateTime, TBL_PROSPECT_ADDRESS_TMP.UPLOAD_DATE);
                result = db.ExecuteNonQuery(cmd);
            }
            catch (Exception) { }
            return result;
        }

        public void SaveProspectBankHistory(ProspectBankHistoryInfo TBL_PROSPECT_BANK_HISTORY_TMP)
        {
            Database db = DBConnection.CreateDatabase();

            DbCommand cmd = db.GetSqlStringCommand(@"
                                UPDATE TBL_PROSPECT_BANK_HISTORY_TMP 
                                SET 
                                SIMPAN_PINJAM = @SIMPAN_PINJAM
                                ,PLAFON = @PLAFON
                                ,JANGKA_BULAN = @JANGKA_BULAN
                                ,MATURITY_DATE = @MATURITY_DATE
                                ,ENTRY_BY = @ENTRY_BY
                                ,ENTRY_DATE = @ENTRY_DATE
                                ,UPDATE_BY = @UPDATE_BY
                                ,UPDATE_DATE = @UPDATE_DATE
                                ,UPLOAD_BY = @UPLOAD_BY
                                ,UPLOAD_DATE = @UPLOAD_DATE
                                WHERE (PROSPECT_ID = @PROSPECT_ID) AND (BANK_NAME = @BANK_NAME);
                                IF @@ROWCOUNT = 0
                                INSERT INTO TBL_PROSPECT_BANK_HISTORY_TMP
                                (
                                    PROSPECT_ID
                                    ,BANK_NAME
                                    ,SIMPAN_PINJAM
                                    ,PLAFON
                                    ,JANGKA_BULAN
                                    ,MATURITY_DATE
                                    ,ENTRY_BY
                                    ,ENTRY_DATE
                                    ,UPDATE_BY
                                    ,UPDATE_DATE
                                    ,UPLOAD_BY
                                    ,UPLOAD_DATE
                                ) 
                                VALUES
                                (
                                    @PROSPECT_ID
                                    ,@BANK_NAME
                                    ,@SIMPAN_PINJAM
                                    ,@PLAFON
                                    ,@JANGKA_BULAN
                                    ,@MATURITY_DATE
                                    ,@ENTRY_BY
                                    ,@ENTRY_DATE
                                    ,@UPDATE_BY
                                    ,@UPDATE_DATE
                                    ,@UPLOAD_BY
                                    ,@UPLOAD_DATE
                                );
                              ");
            db.AddInParameter(cmd, "@PROSPECT_ID", DbType.String, TBL_PROSPECT_BANK_HISTORY_TMP.PROSPECT_ID);
            db.AddInParameter(cmd, "@BANK_NAME", DbType.String, TBL_PROSPECT_BANK_HISTORY_TMP.BANK_NAME);
            db.AddInParameter(cmd, "@SIMPAN_PINJAM", DbType.String, TBL_PROSPECT_BANK_HISTORY_TMP.SIMPAN_PINJAM);
            db.AddInParameter(cmd, "@PLAFON", DbType.Double, TBL_PROSPECT_BANK_HISTORY_TMP.PLAFON);
            db.AddInParameter(cmd, "@JANGKA_BULAN", DbType.Int16, TBL_PROSPECT_BANK_HISTORY_TMP.JANGKA_BULAN);
            db.AddInParameter(cmd, "@MATURITY_DATE", DbType.DateTime, TBL_PROSPECT_BANK_HISTORY_TMP.MATURITY_DATE);
            db.AddInParameter(cmd, "@ENTRY_BY", DbType.String, TBL_PROSPECT_BANK_HISTORY_TMP.ENTRY_BY);
            db.AddInParameter(cmd, "@ENTRY_DATE", DbType.DateTime, TBL_PROSPECT_BANK_HISTORY_TMP.ENTRY_DATE);
            db.AddInParameter(cmd, "@UPDATE_BY", DbType.String, TBL_PROSPECT_BANK_HISTORY_TMP.UPDATE_BY);
            db.AddInParameter(cmd, "@UPDATE_DATE", DbType.DateTime, TBL_PROSPECT_BANK_HISTORY_TMP.UPDATE_DATE);
            db.AddInParameter(cmd, "@UPLOAD_BY", DbType.String, TBL_PROSPECT_BANK_HISTORY_TMP.UPLOAD_BY);
            db.AddInParameter(cmd, "@UPLOAD_DATE", DbType.DateTime, TBL_PROSPECT_BANK_HISTORY_TMP.UPLOAD_DATE);
            db.ExecuteNonQuery(cmd);
        }

        public int SaveProspectBankHistory2(ProspectBankHistoryInfo TBL_PROSPECT_BANK_HISTORY_TMP)
        {
            int result = 0;
            try
            {
                Database db = DBConnection.CreateDatabase();

                DbCommand cmd = db.GetSqlStringCommand(@"
                                UPDATE TBL_PROSPECT_BANK_HISTORY_TMP 
                                SET 
                                SIMPAN_PINJAM = @SIMPAN_PINJAM
                                ,PLAFON = @PLAFON
                                ,JANGKA_BULAN = @JANGKA_BULAN
                                ,MATURITY_DATE = @MATURITY_DATE
                                ,ENTRY_BY = @ENTRY_BY
                                ,ENTRY_DATE = @ENTRY_DATE
                                ,UPDATE_BY = @UPDATE_BY
                                ,UPDATE_DATE = @UPDATE_DATE
                                ,UPLOAD_BY = @UPLOAD_BY
                                ,UPLOAD_DATE = @UPLOAD_DATE
                                WHERE (PROSPECT_ID = @PROSPECT_ID) AND (BANK_NAME = @BANK_NAME);
                                IF @@ROWCOUNT = 0
                                INSERT INTO TBL_PROSPECT_BANK_HISTORY_TMP
                                (
                                    PROSPECT_ID
                                    ,BANK_NAME
                                    ,SIMPAN_PINJAM
                                    ,PLAFON
                                    ,JANGKA_BULAN
                                    ,MATURITY_DATE
                                    ,ENTRY_BY
                                    ,ENTRY_DATE
                                    ,UPDATE_BY
                                    ,UPDATE_DATE
                                    ,UPLOAD_BY
                                    ,UPLOAD_DATE
                                ) 
                                VALUES
                                (
                                    @PROSPECT_ID
                                    ,@BANK_NAME
                                    ,@SIMPAN_PINJAM
                                    ,@PLAFON
                                    ,@JANGKA_BULAN
                                    ,@MATURITY_DATE
                                    ,@ENTRY_BY
                                    ,@ENTRY_DATE
                                    ,@UPDATE_BY
                                    ,@UPDATE_DATE
                                    ,@UPLOAD_BY
                                    ,@UPLOAD_DATE
                                );
                              ");
                db.AddInParameter(cmd, "@PROSPECT_ID", DbType.String, TBL_PROSPECT_BANK_HISTORY_TMP.PROSPECT_ID);
                db.AddInParameter(cmd, "@BANK_NAME", DbType.String, TBL_PROSPECT_BANK_HISTORY_TMP.BANK_NAME);
                db.AddInParameter(cmd, "@SIMPAN_PINJAM", DbType.String, TBL_PROSPECT_BANK_HISTORY_TMP.SIMPAN_PINJAM);
                db.AddInParameter(cmd, "@PLAFON", DbType.Double, TBL_PROSPECT_BANK_HISTORY_TMP.PLAFON);
                db.AddInParameter(cmd, "@JANGKA_BULAN", DbType.Int16, TBL_PROSPECT_BANK_HISTORY_TMP.JANGKA_BULAN);
                db.AddInParameter(cmd, "@MATURITY_DATE", DbType.DateTime, TBL_PROSPECT_BANK_HISTORY_TMP.MATURITY_DATE);
                db.AddInParameter(cmd, "@ENTRY_BY", DbType.String, TBL_PROSPECT_BANK_HISTORY_TMP.ENTRY_BY);
                db.AddInParameter(cmd, "@ENTRY_DATE", DbType.DateTime, TBL_PROSPECT_BANK_HISTORY_TMP.ENTRY_DATE);
                db.AddInParameter(cmd, "@UPDATE_BY", DbType.String, TBL_PROSPECT_BANK_HISTORY_TMP.UPDATE_BY);
                db.AddInParameter(cmd, "@UPDATE_DATE", DbType.DateTime, TBL_PROSPECT_BANK_HISTORY_TMP.UPDATE_DATE);
                db.AddInParameter(cmd, "@UPLOAD_BY", DbType.String, TBL_PROSPECT_BANK_HISTORY_TMP.UPLOAD_BY);
                db.AddInParameter(cmd, "@UPLOAD_DATE", DbType.DateTime, TBL_PROSPECT_BANK_HISTORY_TMP.UPLOAD_DATE);
                result = db.ExecuteNonQuery(cmd);
            }
            catch (Exception) { }
            return result;
        }

        public void SaveProspectDocument(ProspectDocumentInfo TBL_PROSPECT_DOCUMENTS_TMP)
        {
            Database db = DBConnection.CreateDatabase();

            DbCommand cmd = db.GetSqlStringCommand(@"
                                UPDATE TBL_PROSPECT_DOCUMENTS_TMP 
                                SET 
                                    ENTRY_BY = @ENTRY_BY
                                    ,ENTRY_DATE = @ENTRY_DATE
                                    ,UPDATE_BY = @UPDATE_BY
                                    ,UPDATE_DATE = @UPDATE_DATE
                                    ,UPLOAD_BY = @UPLOAD_BY
                                    ,UPLOAD_DATE = @UPLOAD_DATE
                                WHERE (PROSPECT_ID = @PROSPECT_ID) AND (DOCUMENT_ID = @DOCUMENT_ID);
                                IF @@ROWCOUNT = 0
                                INSERT INTO TBL_PROSPECT_DOCUMENTS_TMP
                                (
                                    PROSPECT_ID
                                    ,DOCUMENT_ID
                                    ,ENTRY_BY
                                    ,ENTRY_DATE
                                    ,UPDATE_BY
                                    ,UPDATE_DATE
                                    ,UPLOAD_BY
                                    ,UPLOAD_DATE
                                ) 
                                VALUES
                                (
                                    @PROSPECT_ID
                                    ,@DOCUMENT_ID
                                    ,@ENTRY_BY
                                    ,@ENTRY_DATE
                                    ,@UPDATE_BY
                                    ,@UPDATE_DATE
                                    ,@UPLOAD_BY
                                    ,@UPLOAD_DATE
                                );
                              ");
            db.AddInParameter(cmd, "@PROSPECT_ID", DbType.String, TBL_PROSPECT_DOCUMENTS_TMP.PROSPECT_ID);
            db.AddInParameter(cmd, "@DOCUMENT_ID", DbType.Int16, TBL_PROSPECT_DOCUMENTS_TMP.DOCUMENT_ID);
            db.AddInParameter(cmd, "@ENTRY_BY", DbType.String, TBL_PROSPECT_DOCUMENTS_TMP.ENTRY_BY);
            db.AddInParameter(cmd, "@ENTRY_DATE", DbType.DateTime, TBL_PROSPECT_DOCUMENTS_TMP.ENTRY_DATE);
            db.AddInParameter(cmd, "@UPDATE_BY", DbType.String, TBL_PROSPECT_DOCUMENTS_TMP.UPDATE_BY);
            db.AddInParameter(cmd, "@UPDATE_DATE", DbType.DateTime, TBL_PROSPECT_DOCUMENTS_TMP.UPDATE_DATE);
            db.AddInParameter(cmd, "@UPLOAD_BY", DbType.String, TBL_PROSPECT_DOCUMENTS_TMP.UPLOAD_BY);
            db.AddInParameter(cmd, "@UPLOAD_DATE", DbType.DateTime, TBL_PROSPECT_DOCUMENTS_TMP.UPLOAD_DATE);
            db.ExecuteNonQuery(cmd);
        }

        public int SaveProspectDocument2(ProspectDocumentInfo TBL_PROSPECT_DOCUMENTS_TMP)
        {
            int result = 0;
            try
            {
                Database db = DBConnection.CreateDatabase();

                DbCommand cmd = db.GetSqlStringCommand(@"
                                UPDATE TBL_PROSPECT_DOCUMENTS_TMP 
                                SET 
                                    ENTRY_BY = @ENTRY_BY
                                    ,ENTRY_DATE = @ENTRY_DATE
                                    ,UPDATE_BY = @UPDATE_BY
                                    ,UPDATE_DATE = @UPDATE_DATE
                                    ,UPLOAD_BY = @UPLOAD_BY
                                    ,UPLOAD_DATE = @UPLOAD_DATE
                                WHERE (PROSPECT_ID = @PROSPECT_ID) AND (DOCUMENT_ID = @DOCUMENT_ID);
                                IF @@ROWCOUNT = 0
                                INSERT INTO TBL_PROSPECT_DOCUMENTS_TMP
                                (
                                    PROSPECT_ID
                                    ,DOCUMENT_ID
                                    ,ENTRY_BY
                                    ,ENTRY_DATE
                                    ,UPDATE_BY
                                    ,UPDATE_DATE
                                    ,UPLOAD_BY
                                    ,UPLOAD_DATE
                                ) 
                                VALUES
                                (
                                    @PROSPECT_ID
                                    ,@DOCUMENT_ID
                                    ,@ENTRY_BY
                                    ,@ENTRY_DATE
                                    ,@UPDATE_BY
                                    ,@UPDATE_DATE
                                    ,@UPLOAD_BY
                                    ,@UPLOAD_DATE
                                );
                              ");
                db.AddInParameter(cmd, "@PROSPECT_ID", DbType.String, TBL_PROSPECT_DOCUMENTS_TMP.PROSPECT_ID);
                db.AddInParameter(cmd, "@DOCUMENT_ID", DbType.Int16, TBL_PROSPECT_DOCUMENTS_TMP.DOCUMENT_ID);
                db.AddInParameter(cmd, "@ENTRY_BY", DbType.String, TBL_PROSPECT_DOCUMENTS_TMP.ENTRY_BY);
                db.AddInParameter(cmd, "@ENTRY_DATE", DbType.DateTime, TBL_PROSPECT_DOCUMENTS_TMP.ENTRY_DATE);
                db.AddInParameter(cmd, "@UPDATE_BY", DbType.String, TBL_PROSPECT_DOCUMENTS_TMP.UPDATE_BY);
                db.AddInParameter(cmd, "@UPDATE_DATE", DbType.DateTime, TBL_PROSPECT_DOCUMENTS_TMP.UPDATE_DATE);
                db.AddInParameter(cmd, "@UPLOAD_BY", DbType.String, TBL_PROSPECT_DOCUMENTS_TMP.UPLOAD_BY);
                db.AddInParameter(cmd, "@UPLOAD_DATE", DbType.DateTime, TBL_PROSPECT_DOCUMENTS_TMP.UPLOAD_DATE);
                result = db.ExecuteNonQuery(cmd);
            }
            catch (Exception) { }
            return result;
        }

        public void SaveProspectEmgContact(ProspectEmgContactInfo TBL_PROSPECT_EMG_CONTACT_TMP)
        {
            Database db = DBConnection.CreateDatabase();

            DbCommand cmd = db.GetSqlStringCommand(@"
                                UPDATE TBL_PROSPECT_EMG_CONTACT_TMP 
                                SET 
                                    EMG_CONTACT_NAME = @EMG_CONTACT_NAME
                                    ,EMG_RELATIONSHIP = @EMG_RELATIONSHIP
                                    ,EMG_PHONE_1 = @EMG_PHONE_1
                                    ,EMG_PHONE_2 = @EMG_PHONE_2
                                    ,ENTRY_BY = @ENTRY_BY
                                    ,ENTRY_DATE = @ENTRY_DATE
                                    ,UPDATE_BY = @UPDATE_BY
                                    ,UPDATE_DATE = @UPDATE_DATE
                                    ,UPLOAD_BY = @UPLOAD_BY
                                    ,UPLOAD_DATE = @UPLOAD_DATE
                                WHERE (PROSPECT_ID = @PROSPECT_ID) AND (EMG_CONTACT_ID = @EMG_CONTACT_ID);
                                IF @@ROWCOUNT = 0
                                INSERT INTO TBL_PROSPECT_EMG_CONTACT_TMP
                                (
                                    PROSPECT_ID
                                    ,EMG_CONTACT_ID
                                    ,EMG_CONTACT_NAME
                                    ,EMG_RELATIONSHIP
                                    ,EMG_PHONE_1
                                    ,EMG_PHONE_2
                                    ,ENTRY_BY
                                    ,ENTRY_DATE
                                    ,UPDATE_BY
                                    ,UPDATE_DATE
                                    ,UPLOAD_BY
                                    ,UPLOAD_DATE
                                ) 
                                VALUES
                                (
                                    @PROSPECT_ID
                                    ,@EMG_CONTACT_ID
                                    ,@EMG_CONTACT_NAME
                                    ,@EMG_RELATIONSHIP
                                    ,@EMG_PHONE_1
                                    ,@EMG_PHONE_2
                                    ,@ENTRY_BY
                                    ,@ENTRY_DATE
                                    ,@UPDATE_BY
                                    ,@UPDATE_DATE
                                    ,@UPLOAD_BY
                                    ,@UPLOAD_DATE
                                );
                              ");
            db.AddInParameter(cmd, "@PROSPECT_ID", DbType.String, TBL_PROSPECT_EMG_CONTACT_TMP.PROSPECT_ID);
            db.AddInParameter(cmd, "@EMG_CONTACT_ID", DbType.Byte, TBL_PROSPECT_EMG_CONTACT_TMP.EMG_CONTACT_ID);
            db.AddInParameter(cmd, "@EMG_CONTACT_NAME", DbType.String, TBL_PROSPECT_EMG_CONTACT_TMP.EMG_CONTACT_NAME);
            db.AddInParameter(cmd, "@EMG_RELATIONSHIP", DbType.String, TBL_PROSPECT_EMG_CONTACT_TMP.EMG_RELATIONSHIP);
            db.AddInParameter(cmd, "@EMG_PHONE_1", DbType.String, TBL_PROSPECT_EMG_CONTACT_TMP.EMG_PHONE_1);
            db.AddInParameter(cmd, "@EMG_PHONE_2", DbType.String, TBL_PROSPECT_EMG_CONTACT_TMP.EMG_PHONE_2);
            db.AddInParameter(cmd, "@ENTRY_BY", DbType.String, TBL_PROSPECT_EMG_CONTACT_TMP.ENTRY_BY);
            db.AddInParameter(cmd, "@ENTRY_DATE", DbType.DateTime, TBL_PROSPECT_EMG_CONTACT_TMP.ENTRY_DATE);
            db.AddInParameter(cmd, "@UPDATE_BY", DbType.String, TBL_PROSPECT_EMG_CONTACT_TMP.UPDATE_BY);
            db.AddInParameter(cmd, "@UPDATE_DATE", DbType.DateTime, TBL_PROSPECT_EMG_CONTACT_TMP.UPDATE_DATE);
            db.AddInParameter(cmd, "@UPLOAD_BY", DbType.String, TBL_PROSPECT_EMG_CONTACT_TMP.UPLOAD_BY);
            db.AddInParameter(cmd, "@UPLOAD_DATE", DbType.DateTime, TBL_PROSPECT_EMG_CONTACT_TMP.UPLOAD_DATE);
            db.ExecuteNonQuery(cmd);
        }

        public int SaveProspectEmgContact2(ProspectEmgContactInfo TBL_PROSPECT_EMG_CONTACT_TMP)
        {
            int result = 0;
            try
            {
                Database db = DBConnection.CreateDatabase();

                DbCommand cmd = db.GetSqlStringCommand(@"
                                UPDATE TBL_PROSPECT_EMG_CONTACT_TMP 
                                SET 
                                    EMG_CONTACT_NAME = @EMG_CONTACT_NAME
                                    ,EMG_RELATIONSHIP = @EMG_RELATIONSHIP
                                    ,EMG_PHONE_1 = @EMG_PHONE_1
                                    ,EMG_PHONE_2 = @EMG_PHONE_2
                                    ,ENTRY_BY = @ENTRY_BY
                                    ,ENTRY_DATE = @ENTRY_DATE
                                    ,UPDATE_BY = @UPDATE_BY
                                    ,UPDATE_DATE = @UPDATE_DATE
                                    ,UPLOAD_BY = @UPLOAD_BY
                                    ,UPLOAD_DATE = @UPLOAD_DATE
                                WHERE (PROSPECT_ID = @PROSPECT_ID) AND (EMG_CONTACT_ID = @EMG_CONTACT_ID);
                                IF @@ROWCOUNT = 0
                                INSERT INTO TBL_PROSPECT_EMG_CONTACT_TMP
                                (
                                    PROSPECT_ID
                                    ,EMG_CONTACT_ID
                                    ,EMG_CONTACT_NAME
                                    ,EMG_RELATIONSHIP
                                    ,EMG_PHONE_1
                                    ,EMG_PHONE_2
                                    ,ENTRY_BY
                                    ,ENTRY_DATE
                                    ,UPDATE_BY
                                    ,UPDATE_DATE
                                    ,UPLOAD_BY
                                    ,UPLOAD_DATE
                                ) 
                                VALUES
                                (
                                    @PROSPECT_ID
                                    ,@EMG_CONTACT_ID
                                    ,@EMG_CONTACT_NAME
                                    ,@EMG_RELATIONSHIP
                                    ,@EMG_PHONE_1
                                    ,@EMG_PHONE_2
                                    ,@ENTRY_BY
                                    ,@ENTRY_DATE
                                    ,@UPDATE_BY
                                    ,@UPDATE_DATE
                                    ,@UPLOAD_BY
                                    ,@UPLOAD_DATE
                                );
                              ");
                db.AddInParameter(cmd, "@PROSPECT_ID", DbType.String, TBL_PROSPECT_EMG_CONTACT_TMP.PROSPECT_ID);
                db.AddInParameter(cmd, "@EMG_CONTACT_ID", DbType.Byte, TBL_PROSPECT_EMG_CONTACT_TMP.EMG_CONTACT_ID);
                db.AddInParameter(cmd, "@EMG_CONTACT_NAME", DbType.String, TBL_PROSPECT_EMG_CONTACT_TMP.EMG_CONTACT_NAME);
                db.AddInParameter(cmd, "@EMG_RELATIONSHIP", DbType.String, TBL_PROSPECT_EMG_CONTACT_TMP.EMG_RELATIONSHIP);
                db.AddInParameter(cmd, "@EMG_PHONE_1", DbType.String, TBL_PROSPECT_EMG_CONTACT_TMP.EMG_PHONE_1);
                db.AddInParameter(cmd, "@EMG_PHONE_2", DbType.String, TBL_PROSPECT_EMG_CONTACT_TMP.EMG_PHONE_2);
                db.AddInParameter(cmd, "@ENTRY_BY", DbType.String, TBL_PROSPECT_EMG_CONTACT_TMP.ENTRY_BY);
                db.AddInParameter(cmd, "@ENTRY_DATE", DbType.DateTime, TBL_PROSPECT_EMG_CONTACT_TMP.ENTRY_DATE);
                db.AddInParameter(cmd, "@UPDATE_BY", DbType.String, TBL_PROSPECT_EMG_CONTACT_TMP.UPDATE_BY);
                db.AddInParameter(cmd, "@UPDATE_DATE", DbType.DateTime, TBL_PROSPECT_EMG_CONTACT_TMP.UPDATE_DATE);
                db.AddInParameter(cmd, "@UPLOAD_BY", DbType.String, TBL_PROSPECT_EMG_CONTACT_TMP.UPLOAD_BY);
                db.AddInParameter(cmd, "@UPLOAD_DATE", DbType.DateTime, TBL_PROSPECT_EMG_CONTACT_TMP.UPLOAD_DATE);
                result = db.ExecuteNonQuery(cmd);
            }
            catch (Exception) { }
            return result;
        }

        public void SaveProspectVisit(ProspectVisitInfo TBL_PROSPECT_VISIT_TMP)
        {
            Database db = DBConnection.CreateDatabase();

            DbCommand cmd = db.GetSqlStringCommand(@"
                                UPDATE TBL_PROSPECT_VISIT_TMP 
                                SET 
                                    OBJECTIVE_OF_VISIT = @OBJECTIVE_OF_VISIT
                                    ,DATE_VISIT_PLAN = @DATE_VISIT_PLAN
                                    ,DATE_VISIT_ACTUAL = @DATE_VISIT_ACTUAL
                                    ,JOINT_FIELD_WORK = @JOINT_FIELD_WORK
                                    ,RESULT_OF_VISIT = @RESULT_OF_VISIT
                                    ,REMARK = @REMARK
                                    ,ENTRY_BY = @ENTRY_BY
                                    ,ENTRY_DATE = @ENTRY_DATE
                                    ,UPDATE_BY = @UPDATE_BY
                                    ,UPDATE_DATE = @UPDATE_DATE
                                    ,UPLOAD_BY = @UPLOAD_BY
                                    ,UPLOAD_DATE = @UPLOAD_DATE
                                WHERE (VISIT_NO = @VISIT_NO) AND (PROSPECT_ID = @PROSPECT_ID);
                                IF @@ROWCOUNT = 0
                                INSERT INTO TBL_PROSPECT_VISIT_TMP
                                (
                                    PROSPECT_ID
                                    ,VISIT_NO
                                    ,OBJECTIVE_OF_VISIT
                                    ,DATE_VISIT_PLAN
                                    ,DATE_VISIT_ACTUAL
                                    ,JOINT_FIELD_WORK
                                    ,RESULT_OF_VISIT
                                    ,REMARK
                                    ,ENTRY_BY
                                    ,ENTRY_DATE
                                    ,UPDATE_BY
                                    ,UPDATE_DATE
                                    ,UPLOAD_BY
                                    ,UPLOAD_DATE
                                ) 
                                VALUES
                                (
                                    @PROSPECT_ID
                                    ,@VISIT_NO
                                    ,@OBJECTIVE_OF_VISIT
                                    ,@DATE_VISIT_PLAN
                                    ,@DATE_VISIT_ACTUAL
                                    ,@JOINT_FIELD_WORK
                                    ,@RESULT_OF_VISIT
                                    ,@REMARK
                                    ,@ENTRY_BY
                                    ,@ENTRY_DATE
                                    ,@UPDATE_BY
                                    ,@UPDATE_DATE
                                    ,@UPLOAD_BY
                                    ,@UPLOAD_DATE
                                );
                            ");
            db.AddInParameter(cmd, "@PROSPECT_ID", DbType.String, TBL_PROSPECT_VISIT_TMP.PROSPECT_ID);
            db.AddInParameter(cmd, "@VISIT_NO", DbType.Byte, TBL_PROSPECT_VISIT_TMP.VISIT_NO);
            db.AddInParameter(cmd, "@OBJECTIVE_OF_VISIT", DbType.String, TBL_PROSPECT_VISIT_TMP.OBJECTIVE_OF_VISIT);
            db.AddInParameter(cmd, "@DATE_VISIT_PLAN", DbType.DateTime, TBL_PROSPECT_VISIT_TMP.DATE_VISIT_PLAN);
            db.AddInParameter(cmd, "@DATE_VISIT_ACTUAL", DbType.DateTime, TBL_PROSPECT_VISIT_TMP.DATE_VISIT_ACTUAL);
            db.AddInParameter(cmd, "@JOINT_FIELD_WORK", DbType.String, TBL_PROSPECT_VISIT_TMP.JOINT_FIELD_WORK);
            db.AddInParameter(cmd, "@RESULT_OF_VISIT", DbType.String, TBL_PROSPECT_VISIT_TMP.RESULT_OF_VISIT);
            db.AddInParameter(cmd, "@REMARK", DbType.String, TBL_PROSPECT_VISIT_TMP.REMARK);
            db.AddInParameter(cmd, "@ENTRY_BY", DbType.String, TBL_PROSPECT_VISIT_TMP.ENTRY_BY);
            db.AddInParameter(cmd, "@ENTRY_DATE", DbType.DateTime, TBL_PROSPECT_VISIT_TMP.ENTRY_DATE);
            db.AddInParameter(cmd, "@UPDATE_BY", DbType.String, TBL_PROSPECT_VISIT_TMP.UPDATE_BY);
            db.AddInParameter(cmd, "@UPDATE_DATE", DbType.DateTime, TBL_PROSPECT_VISIT_TMP.UPDATE_DATE);
            db.AddInParameter(cmd, "@UPLOAD_BY", DbType.String, TBL_PROSPECT_VISIT_TMP.UPLOAD_BY);
            db.AddInParameter(cmd, "@UPLOAD_DATE", DbType.DateTime, TBL_PROSPECT_VISIT_TMP.UPLOAD_DATE);
            db.ExecuteNonQuery(cmd);
        }

        public int SaveProspectVisit2(ProspectVisitInfo TBL_PROSPECT_VISIT_TMP)
        {
            int result = 0;
            try
            {
                Database db = DBConnection.CreateDatabase();

                DbCommand cmd = db.GetSqlStringCommand(@"
                                UPDATE TBL_PROSPECT_VISIT_TMP 
                                SET 
                                    OBJECTIVE_OF_VISIT = @OBJECTIVE_OF_VISIT
                                    ,DATE_VISIT_PLAN = @DATE_VISIT_PLAN
                                    ,DATE_VISIT_ACTUAL = @DATE_VISIT_ACTUAL
                                    ,JOINT_FIELD_WORK = @JOINT_FIELD_WORK
                                    ,RESULT_OF_VISIT = @RESULT_OF_VISIT
                                    ,REMARK = @REMARK
                                    ,ENTRY_BY = @ENTRY_BY
                                    ,ENTRY_DATE = @ENTRY_DATE
                                    ,UPDATE_BY = @UPDATE_BY
                                    ,UPDATE_DATE = @UPDATE_DATE
                                    ,UPLOAD_BY = @UPLOAD_BY
                                    ,UPLOAD_DATE = @UPLOAD_DATE
                                WHERE (VISIT_NO = @VISIT_NO) AND (PROSPECT_ID = @PROSPECT_ID);
                                IF @@ROWCOUNT = 0
                                INSERT INTO TBL_PROSPECT_VISIT_TMP
                                (
                                    PROSPECT_ID
                                    ,VISIT_NO
                                    ,OBJECTIVE_OF_VISIT
                                    ,DATE_VISIT_PLAN
                                    ,DATE_VISIT_ACTUAL
                                    ,JOINT_FIELD_WORK
                                    ,RESULT_OF_VISIT
                                    ,REMARK
                                    ,ENTRY_BY
                                    ,ENTRY_DATE
                                    ,UPDATE_BY
                                    ,UPDATE_DATE
                                    ,UPLOAD_BY
                                    ,UPLOAD_DATE
                                ) 
                                VALUES
                                (
                                    @PROSPECT_ID
                                    ,@VISIT_NO
                                    ,@OBJECTIVE_OF_VISIT
                                    ,@DATE_VISIT_PLAN
                                    ,@DATE_VISIT_ACTUAL
                                    ,@JOINT_FIELD_WORK
                                    ,@RESULT_OF_VISIT
                                    ,@REMARK
                                    ,@ENTRY_BY
                                    ,@ENTRY_DATE
                                    ,@UPDATE_BY
                                    ,@UPDATE_DATE
                                    ,@UPLOAD_BY
                                    ,@UPLOAD_DATE
                                );
                            ");
                db.AddInParameter(cmd, "@PROSPECT_ID", DbType.String, TBL_PROSPECT_VISIT_TMP.PROSPECT_ID);
                db.AddInParameter(cmd, "@VISIT_NO", DbType.Byte, TBL_PROSPECT_VISIT_TMP.VISIT_NO);
                db.AddInParameter(cmd, "@OBJECTIVE_OF_VISIT", DbType.String, TBL_PROSPECT_VISIT_TMP.OBJECTIVE_OF_VISIT);
                db.AddInParameter(cmd, "@DATE_VISIT_PLAN", DbType.DateTime, TBL_PROSPECT_VISIT_TMP.DATE_VISIT_PLAN);
                db.AddInParameter(cmd, "@DATE_VISIT_ACTUAL", DbType.DateTime, TBL_PROSPECT_VISIT_TMP.DATE_VISIT_ACTUAL);
                db.AddInParameter(cmd, "@JOINT_FIELD_WORK", DbType.String, TBL_PROSPECT_VISIT_TMP.JOINT_FIELD_WORK);
                db.AddInParameter(cmd, "@RESULT_OF_VISIT", DbType.String, TBL_PROSPECT_VISIT_TMP.RESULT_OF_VISIT);
                db.AddInParameter(cmd, "@REMARK", DbType.String, TBL_PROSPECT_VISIT_TMP.REMARK);
                db.AddInParameter(cmd, "@ENTRY_BY", DbType.String, TBL_PROSPECT_VISIT_TMP.ENTRY_BY);
                db.AddInParameter(cmd, "@ENTRY_DATE", DbType.DateTime, TBL_PROSPECT_VISIT_TMP.ENTRY_DATE);
                db.AddInParameter(cmd, "@UPDATE_BY", DbType.String, TBL_PROSPECT_VISIT_TMP.UPDATE_BY);
                db.AddInParameter(cmd, "@UPDATE_DATE", DbType.DateTime, TBL_PROSPECT_VISIT_TMP.UPDATE_DATE);
                db.AddInParameter(cmd, "@UPLOAD_BY", DbType.String, TBL_PROSPECT_VISIT_TMP.UPLOAD_BY);
                db.AddInParameter(cmd, "@UPLOAD_DATE", DbType.DateTime, TBL_PROSPECT_VISIT_TMP.UPLOAD_DATE);
                result = db.ExecuteNonQuery(cmd);
            }
            catch (Exception) { }
            return result;
        }

        public void SaveProspectRefferal(ProspectRefferalInfo TBL_PROSPECT_REFFERAL)
        {
            Database db = DBConnection.CreateDatabase();

            DbCommand cmd = db.GetSqlStringCommand(@"
                                UPDATE TBL_PROSPECT_REFFERAL 
                                SET 
                                    FULL_NAME = @FULL_NAME
                                    ,JENIS_USAHA = @JENIS_USAHA
                                    ,OFFICE_PHONE = @OFFICE_PHONE
                                    ,RENCANA_PLAFON = @RENCANA_PLAFON
                                    ,ADDRESS_BUSINESS = LEFT(@ADDRESS_BUSINESS,255)
                                    ,UNIT_CODE_SOURCE = @UNIT_CODE_SOURCE
                                    ,UNIT_CODE_DEST = @UNIT_CODE_DEST
                                    ,AO_CODE_ORIG = @AO_CODE_ORIG
                                    ,ENTRY_BY = @ENTRY_BY
                                    ,ENTRY_DATE = @ENTRY_DATE
                                    ,UPDATE_BY = @UPDATE_BY
                                    ,UPDATE_DATE = @UPDATE_DATE
                                    ,UPLOAD_BY = @UPLOAD_BY
                                    ,UPLOAD_DATE = @UPLOAD_DATE
                                    ,IS_DOWNLOADED = @IS_DOWNLOADED
                                    ,DOWNLOAD_BY = @DOWNLOAD_BY
                                    ,DOWNLOAD_DATE = @DOWNLOAD_DATE
                                WHERE (REFFERAL_ID = @REFFERAL_ID);
                                IF @@ROWCOUNT = 0
                                INSERT INTO TBL_PROSPECT_REFFERAL
                                (
                                    REFFERAL_ID
                                    ,FULL_NAME
                                    ,JENIS_USAHA
                                    ,OFFICE_PHONE
                                    ,RENCANA_PLAFON
                                    ,ADDRESS_BUSINESS
                                    ,UNIT_CODE_SOURCE
                                    ,UNIT_CODE_DEST
                                    ,AO_CODE_ORIG
                                    ,ENTRY_BY
                                    ,ENTRY_DATE
                                    ,UPDATE_BY
                                    ,UPDATE_DATE
                                    ,UPLOAD_BY
                                    ,UPLOAD_DATE
                                    ,IS_DOWNLOADED
                                    ,DOWNLOAD_BY
                                    ,DOWNLOAD_DATE
                                ) 
                                VALUES
                                (
                                    @REFFERAL_ID
                                    ,@FULL_NAME
                                    ,@JENIS_USAHA
                                    ,@OFFICE_PHONE
                                    ,@RENCANA_PLAFON
                                    ,LEFT(@ADDRESS_BUSINESS,255)
                                    ,@UNIT_CODE_SOURCE
                                    ,@UNIT_CODE_DEST
                                    ,@AO_CODE_ORIG
                                    ,@ENTRY_BY
                                    ,@ENTRY_DATE
                                    ,@UPDATE_BY
                                    ,@UPDATE_DATE
                                    ,@UPLOAD_BY
                                    ,@UPLOAD_DATE
                                    ,@IS_DOWNLOADED
                                    ,@DOWNLOAD_BY
                                    ,@DOWNLOAD_DATE
                                );
                            ");
            db.AddInParameter(cmd, "@REFFERAL_ID", DbType.String, TBL_PROSPECT_REFFERAL.REFFERAL_ID);
            db.AddInParameter(cmd, "@FULL_NAME", DbType.String, TBL_PROSPECT_REFFERAL.FULL_NAME);
            db.AddInParameter(cmd, "@JENIS_USAHA", DbType.String, TBL_PROSPECT_REFFERAL.JENIS_USAHA);
            db.AddInParameter(cmd, "@OFFICE_PHONE", DbType.String, TBL_PROSPECT_REFFERAL.OFFICE_PHONE);
            db.AddInParameter(cmd, "@RENCANA_PLAFON", DbType.Double, TBL_PROSPECT_REFFERAL.RENCANA_PLAFON);
            db.AddInParameter(cmd, "@ADDRESS_BUSINESS", DbType.String, TBL_PROSPECT_REFFERAL.ADDRESS_BUSINESS);
            db.AddInParameter(cmd, "@UNIT_CODE_SOURCE", DbType.String, TBL_PROSPECT_REFFERAL.UNIT_CODE_SOURCE);
            db.AddInParameter(cmd, "@UNIT_CODE_DEST", DbType.String, TBL_PROSPECT_REFFERAL.UNIT_CODE_DEST);
            db.AddInParameter(cmd, "@AO_CODE_ORIG", DbType.String, TBL_PROSPECT_REFFERAL.AO_CODE_ORIG);
            db.AddInParameter(cmd, "@ENTRY_BY", DbType.String, TBL_PROSPECT_REFFERAL.ENTRY_BY);
            db.AddInParameter(cmd, "@ENTRY_DATE", DbType.DateTime, TBL_PROSPECT_REFFERAL.ENTRY_DATE);
            db.AddInParameter(cmd, "@UPDATE_BY", DbType.String, TBL_PROSPECT_REFFERAL.UPDATE_BY);
            db.AddInParameter(cmd, "@UPDATE_DATE", DbType.DateTime, TBL_PROSPECT_REFFERAL.UPDATE_DATE);
            db.AddInParameter(cmd, "@UPLOAD_BY", DbType.String, TBL_PROSPECT_REFFERAL.UPLOAD_BY);
            db.AddInParameter(cmd, "@UPLOAD_DATE", DbType.DateTime, TBL_PROSPECT_REFFERAL.UPLOAD_DATE);
            db.AddInParameter(cmd, "@IS_DOWNLOADED", DbType.String, TBL_PROSPECT_REFFERAL.IS_DOWNLOADED);
            db.AddInParameter(cmd, "@DOWNLOAD_BY", DbType.String, TBL_PROSPECT_REFFERAL.DOWNLOAD_BY);
            db.AddInParameter(cmd, "@DOWNLOAD_DATE", DbType.DateTime, TBL_PROSPECT_REFFERAL.DOWNLOAD_DATE);
            db.ExecuteNonQuery(cmd);
        }

        public int SaveProspectRefferal2(ProspectRefferalInfo TBL_PROSPECT_REFFERAL)
        {
            int result = 0;
            try
            {
                Database db = DBConnection.CreateDatabase();

                DbCommand cmd = db.GetSqlStringCommand(@"
                                UPDATE TBL_PROSPECT_REFFERAL 
                                SET IS_DOWNLOADED = '0', IS_ASSIGNED = '0' 
                                WHERE (REFFERAL_ID = @REFFERAL_ID);
                                IF @@ROWCOUNT = 0
                                INSERT INTO TBL_PROSPECT_REFFERAL
                                (
                                    REFFERAL_ID
                                    ,FULL_NAME
                                    ,JENIS_USAHA
                                    ,OFFICE_PHONE
                                    ,RENCANA_PLAFON
                                    ,ADDRESS_BUSINESS
                                    ,UNIT_CODE_SOURCE
                                    ,UNIT_CODE_DEST
                                    ,AO_CODE_ORIG
                                    ,ENTRY_BY
                                    ,ENTRY_DATE
                                    ,UPDATE_BY
                                    ,UPDATE_DATE
                                    ,UPLOAD_BY
                                    ,UPLOAD_DATE
                                ) 
                                VALUES
                                (
                                    @REFFERAL_ID
                                    ,@FULL_NAME
                                    ,@JENIS_USAHA
                                    ,@OFFICE_PHONE
                                    ,@RENCANA_PLAFON
                                    ,LEFT(@ADDRESS_BUSINESS,255)
                                    ,@UNIT_CODE_SOURCE
                                    ,@UNIT_CODE_DEST
                                    ,@AO_CODE_ORIG
                                    ,@ENTRY_BY
                                    ,@ENTRY_DATE
                                    ,@UPDATE_BY
                                    ,@UPDATE_DATE
                                    ,@UPLOAD_BY
                                    ,@UPLOAD_DATE
                                );
                            ");
                db.AddInParameter(cmd, "@REFFERAL_ID", DbType.String, TBL_PROSPECT_REFFERAL.REFFERAL_ID);
                db.AddInParameter(cmd, "@FULL_NAME", DbType.String, TBL_PROSPECT_REFFERAL.FULL_NAME);
                db.AddInParameter(cmd, "@JENIS_USAHA", DbType.String, TBL_PROSPECT_REFFERAL.JENIS_USAHA);
                db.AddInParameter(cmd, "@OFFICE_PHONE", DbType.String, TBL_PROSPECT_REFFERAL.OFFICE_PHONE);
                db.AddInParameter(cmd, "@RENCANA_PLAFON", DbType.Double, TBL_PROSPECT_REFFERAL.RENCANA_PLAFON);
                db.AddInParameter(cmd, "@ADDRESS_BUSINESS", DbType.String, TBL_PROSPECT_REFFERAL.ADDRESS_BUSINESS);
                db.AddInParameter(cmd, "@UNIT_CODE_SOURCE", DbType.String, TBL_PROSPECT_REFFERAL.UNIT_CODE_SOURCE);
                db.AddInParameter(cmd, "@UNIT_CODE_DEST", DbType.String, TBL_PROSPECT_REFFERAL.UNIT_CODE_DEST);
                db.AddInParameter(cmd, "@AO_CODE_ORIG", DbType.String, TBL_PROSPECT_REFFERAL.AO_CODE_ORIG);
                db.AddInParameter(cmd, "@ENTRY_BY", DbType.String, TBL_PROSPECT_REFFERAL.ENTRY_BY);
                db.AddInParameter(cmd, "@ENTRY_DATE", DbType.DateTime, TBL_PROSPECT_REFFERAL.ENTRY_DATE);
                db.AddInParameter(cmd, "@UPDATE_BY", DbType.String, TBL_PROSPECT_REFFERAL.UPDATE_BY);
                db.AddInParameter(cmd, "@UPDATE_DATE", DbType.DateTime, TBL_PROSPECT_REFFERAL.UPDATE_DATE);
                db.AddInParameter(cmd, "@UPLOAD_BY", DbType.String, TBL_PROSPECT_REFFERAL.UPLOAD_BY);
                db.AddInParameter(cmd, "@UPLOAD_DATE", DbType.DateTime, TBL_PROSPECT_REFFERAL.UPLOAD_DATE);
                result = db.ExecuteNonQuery(cmd);
            }
            catch (Exception) { }
            return result;
        }

        public int UpdateKeputusanUM(ProspectInfo Info)
        {
            int status = 0;
            try
            {
                Database db = DBConnection.CreateDatabase();

                DbCommand cmd = db.GetSqlStringCommand(@"
                              DECLARE @PROGRESS AS VARCHAR(10) 
                                IF (@UM_DECISION = '1') SET @PROGRESS = 'CD'
                                IF (@UM_DECISION = '0') SET @PROGRESS = 'RJU'
                              UPDATE TBL_PROSPECT 
                                SET UM_DECISION=@UM_DECISION, 
                                    UM_DECISION_NOTE=@UM_DECISION_NOTE, 
                                    UM_DECISION_DATE=@UM_DECISION_DATE, 
                                    UPDATE_BY=@UPDATE_BY, 
                                    UPDATE_DATE=@UPDATE_DATE, 
                                    PROSPECT_FINISH_TIME=@PROSPECT_FINISH_TIME, 
                                    CO_ASSIGNED=@CO_ASSIGNED, 
                                    PROGRESS_STATUS=@PROGRESS,
                                    REPUTASI_DEBITUR='SPAWEB' 
                              WHERE PROSPECT_ID=@PROSPECT_ID

                                UPDATE TBL_PROSPECT_UM 
                                SET UM_DECISION=@UM_DECISION, 
                                    UM_DECISION_NOTE=@UM_DECISION_NOTE, 
                                    UM_DECISION_DATE=@UM_DECISION_DATE, 
                                    UPDATE_BY=@UPDATE_BY, 
                                    UPDATE_DATE=@UPDATE_DATE, 
                                    PROSPECT_FINISH_TIME=@PROSPECT_FINISH_TIME, 
                                    CO_ASSIGNED=@CO_ASSIGNED, 
                                    PROGRESS_STATUS=@PROGRESS,
                                    REPUTASI_DEBITUR='SPAWEB'  
                              WHERE PROSPECT_ID=@PROSPECT_ID
                                
                              UPDATE TBL_PROSPECT_UPDATE 
                                SET UPDATE_DATE = @UPDATE_DATE,
                                IS_FORCE = '1'
                              WHERE PROSPECT_ID=@PROSPECT_ID
                              IF @@ROWCOUNT = 0
                              INSERT INTO TBL_PROSPECT_UPDATE (PROSPECT_ID, UPDATE_DATE, IS_FORCE) 
                                VALUES (@PROSPECT_ID, @UPDATE_DATE, '1')");
                db.AddInParameter(cmd, "@PROSPECT_ID", DbType.String, Info.PROSPECT_ID);
                db.AddInParameter(cmd, "@UM_DECISION", DbType.String, Info.UM_DECISION);
                db.AddInParameter(cmd, "@UM_DECISION_NOTE", DbType.String, Info.UM_DECISION_NOTE);
                db.AddInParameter(cmd, "@UM_DECISION_DATE", DbType.DateTime, Info.UM_DECISION_DATE);
                db.AddInParameter(cmd, "@UPDATE_BY", DbType.String, Info.UPDATE_BY);
                db.AddInParameter(cmd, "@UPDATE_DATE", DbType.DateTime, Info.UPDATE_DATE.AddMinutes(15));
                db.AddInParameter(cmd, "@PROSPECT_FINISH_TIME", DbType.DateTime, Info.PROSPECT_FINISH_TIME);
                db.AddInParameter(cmd, "@CO_ASSIGNED", DbType.String, Info.CO_ASSIGNED);

                status = db.ExecuteNonQuery(cmd);
            }
            catch (Exception ex) { }
            return status;
        }

        public List<Shuba.Model.DedupResultInfo> GetDedupResult(string PROSPECT_ID, Int16 NO_OF_REQUEST)
        {
            List<Shuba.Model.DedupResultInfo> _list = new List<Shuba.Model.DedupResultInfo>();
            Database mydb = DBConnection.CreateDatabase();

            string mysql = String.Format(@"SELECT TOP 15 [DEDUP_DATE],[PRN_ORIGINAL],[BRANCH_CODE_ORI]
                  ,[PRN_MATCH],[KTP_NAME_MATCH],[ADDRESS_BUSINESS_MATCH]
                  ,[DOB_MATCH],[PLACE_OF_BIRTH_MATCH],[KTP_NO_MATCH]
                  ,ISNULL(P.PRODUCT_NAME, '') PRODUCT_TYPE_MATCH,[PROGRESS_STATUS_MATCH],[KRITERIA],[UPDATE_DATE] 
            FROM [TBL_DEDUP_RESULT] DR
            LEFT JOIN (SELECT PRODUCT_ID, PRODUCT_NAME FROM TBL_MS_PRODUCTS) AS P ON DR.PRODUCT_TYPE_MATCH = P.PRODUCT_ID 
            WHERE DR.PROSPECT_ID = @PROSPECT_ID 
                AND DR.NO_OF_REQUEST = @NO_OF_REQUEST
            ORDER BY DR.DEDUP_DATE DESC");
            DbCommand mycomm = mydb.GetSqlStringCommand(mysql);
            mydb.AddInParameter(mycomm, "@PROSPECT_ID", DbType.String, PROSPECT_ID);
            mydb.AddInParameter(mycomm, "@NO_OF_REQUEST", DbType.Int16, NO_OF_REQUEST);

            using (IDataReader dr = mydb.ExecuteReader(mycomm))
            {
                while (dr.Read())
                {
                    _list.Add(new Shuba.Model.DedupResultInfo(dr.GetDateTime(0), dr.GetString(1), dr.GetString(2), dr.GetString(3), dr.GetString(4), dr.GetString(5), dr.GetDateTime(6), dr.GetString(7), dr.GetString(8), dr.GetString(9), dr.GetString(10), dr.GetString(11), dr.GetDateTime(12)));
                }
                dr.Close();
            }
            return _list;
        }

        public DateTime GetDateDedupResult(string PROSPECT_ID)
        {
            Database db = DBConnection.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(@"SELECT MAX(END_DATE)
            FROM TBL_DEDUP_CHECKING_REQ (NOLOCK)
            WHERE PROSPECT_ID = @PROSPECT_ID 
	            AND PROCESS_STATE = 2");

            db.AddInParameter(cmd, "@PROSPECT_ID", DbType.String, PROSPECT_ID);

            object obj = db.ExecuteScalar(cmd);
            if (obj != null)
            {
                if (obj.ToString() != "") return Convert.ToDateTime(obj);
            }
            return new DateTime(2009, 1, 1);
        }

        public Int16 GetRequestDedupKe(string PROSPECT_ID)
        {
            Database db = DBConnection.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(@"SELECT MAX(NO_OF_REQUEST)
            FROM TBL_DEDUP_CHECKING_REQ (NOLOCK)
            WHERE PROSPECT_ID = @PROSPECT_ID 
	            AND PROCESS_STATE = 2");

            db.AddInParameter(cmd, "@PROSPECT_ID", DbType.String, PROSPECT_ID);

            object obj = db.ExecuteScalar(cmd);
            if (obj != null)
            {
                if (obj.ToString() != "") return Convert.ToInt16(obj);
            }
            return 0;
        }

        public int RequestDedup(string PROSPECT_ID)
        {
            int result = 0;
            Database db = DBConnection.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(@"
            DECLARE @REQ    INT;
            
            SELECT @REQ = COUNT(0) FROM TBL_DEDUP_CHECKING_REQ
            WHERE PROCESS_STATE = '1' AND PROSPECT_ID = @PROSPECT_ID

            IF @REQ = 0
            BEGIN

                DECLARE @PROSPECT_NO        VARCHAR(19),
                        @UNIT_CODE          VARCHAR(6),
                        @KTP_NAME           VARCHAR(50),
                        @PLACE_OF_BIRTH     VARCHAR(50),
                        @DATE_OF_BIRTH      DATETIME,
                        @KTP_NO             VARCHAR(50),
                        @ADDRESS_BUSINESS   VARCHAR(255),
                        @MOBILE_PHONE       VARCHAR(18),
                        @RESIDENTIAL_PHONE  VARCHAR(12),
                        @OFFICE_PHONE       VARCHAR(12),
                        @PCD_DATE           DATETIME,
                        @NO_OF_CALLS        SMALLINT;
            
                SELECT @PROSPECT_ID = P.[PROSPECT_ID], @PROSPECT_NO = P.[PROSPECT_NO], @UNIT_CODE = P.[UNIT_CODE]
                  ,@KTP_NAME = P.[KTP_NAME], @PLACE_OF_BIRTH = P.[PLACE_OF_BIRTH], @DATE_OF_BIRTH = P.[DATE_OF_BIRTH]
                  ,@KTP_NO = P.[KTP_NO], @ADDRESS_BUSINESS = P.[ADDRESS_BUSINESS], @MOBILE_PHONE = P.[MOBILE_PHONE]
                  ,@RESIDENTIAL_PHONE = P.[RESIDENTIAL_PHONE], @OFFICE_PHONE = P.[OFFICE_PHONE], @PCD_DATE = PG.ENTRY_DATE, @NO_OF_CALLS = P.[NO_OF_CALLS]
                FROM TBL_PROSPECT P 
                LEFT OUTER JOIN (SELECT PROSPECT_ID, ENTRY_DATE, PROGRESS_STATUS FROM TBL_PROSPECT_PROGRESS) PG
                ON P.PROSPECT_ID = PG.PROSPECT_ID
                WHERE P.PROSPECT_ID = @PROSPECT_ID AND PG.PROGRESS_STATUS = 'PCD'


                 UPDATE [TBL_DEDUP_CHECKING_REQ_TMP]
                 SET  
                       [PROSPECT_NO] = @PROSPECT_NO
                      ,[UNIT_CODE] = @UNIT_CODE
                      ,[KTP_NAME] = @KTP_NAME
                      ,[PLACE_OF_BIRTH] = @PLACE_OF_BIRTH
                      ,[DATE_OF_BIRTH] = @DATE_OF_BIRTH
                      ,[KTP_NO] = @KTP_NO
                      ,[ADDRESS_BUSINESS] = @ADDRESS_BUSINESS
                      ,[MOBILE_PHONE] = @MOBILE_PHONE
                      ,[RESIDENTIAL_PHONE] = @RESIDENTIAL_PHONE
                      ,[OFFICE_PHONE] = @OFFICE_PHONE
                      ,[PCD_DATE] = @PCD_DATE
                      ,[NO_OF_REQUEST] = @NO_OF_CALLS + 1
                 WHERE [PROSPECT_ID] = @PROSPECT_ID 

                IF @@ROWCOUNT = 0

                 INSERT INTO [TBL_DEDUP_CHECKING_REQ_TMP]
                       ([PROSPECT_ID],[PROSPECT_NO],[UNIT_CODE],[KTP_NAME]
                       ,[PLACE_OF_BIRTH],[DATE_OF_BIRTH]
                       ,[KTP_NO],[ADDRESS_BUSINESS]
                       ,[MOBILE_PHONE],[RESIDENTIAL_PHONE]
                       ,[OFFICE_PHONE],[PCD_DATE]
                       ,[NO_OF_REQUEST])
                 VALUES
                       (@PROSPECT_ID,@PROSPECT_NO,@UNIT_CODE,@KTP_NAME
                       ,@PLACE_OF_BIRTH,@DATE_OF_BIRTH
                       ,@KTP_NO,@ADDRESS_BUSINESS
                       ,@MOBILE_PHONE,@RESIDENTIAL_PHONE
                       ,@OFFICE_PHONE,@PCD_DATE
                       ,@NO_OF_CALLS + 1)

               SET @REQ = 0;
            END

            SELECT @REQ;
            ");

            db.AddInParameter(cmd, "@PROSPECT_ID", DbType.String, PROSPECT_ID);
            object obj = db.ExecuteScalar(cmd);
            if (obj != null)
            {
                if (obj.ToString() != "") result = Convert.ToInt32(obj);
            }
            return result;
        }

        public bool IsHasRequest(string PROSPECT_ID)
        {
            bool isreq = false;

            Database mydb = DBConnection.CreateDatabase();

            string mysql = String.Format(@"SELECT PROSPECT_ID FROM TBL_DEDUP_CHECKING_REQ (NOLOCK) WHERE PROSPECT_ID = @PROSPECT_ID AND PROCESS_STATE = 2;");
            DbCommand mycomm = mydb.GetSqlStringCommand(mysql);
            mydb.AddInParameter(mycomm, "@PROSPECT_ID", DbType.String, PROSPECT_ID);

            using (IDataReader dr = mydb.ExecuteReader(mycomm))
            {
                while (dr.Read())
                {
                    isreq = true;                    
                }
                dr.Close();
            }
            return isreq;
        }
    }
}